E-Business Suite: Enabling multiple org through back end by package in oracle apps R12.1.3 Enclosed the pkb and pks query here

By SQL Developer for one particular operating unit:

How to find which org_id is currently set in back end

set serveroutput on;
declare
gn_org_id NUMBER ;
begin
gn_org_id := apps.fnd_profile.VALUE('ORG_ID');
dbms_output.put_line(gn_org_id);
end;

How to set Org id in back end

mo_global.init('ONT'); -- order management
mo_global.set_policy_context('S','5758');
fnd_global.apps_initialize(0,52123,660); -- pass in user_id, responsibility_id, and application_id

or
simply give

exec mo_global.set_policy_context('S',6359);

By Report-
Whenever the report needs to run for multiple operating unit then while submitting the report the user selects Ireland operating unit in parameter then it will goes to the report as organization id of that op unit and from the org id the query gets this org id and get executed.

create or replace PACKAGE XX_CUST_DQM_TRX_PKG AUTHID CURRENT_USER
AS

/*-- +==================================================================================+
-- | Name : XX_CUST_DQM_TRX_PKG |
-- | |
-- | Created By : NA |
-- | Called From : Concurrent Program |
-- | |
-- | CHANGE HISTORY: |
-- | |
-- |Version Date Name Description |
-- |------- -------- ------------- ------------------------- |
-- |Draft NA NA Initial Draft Version |
-- |1.1 22-Apr-2014 sathish CR#205238 Commented p_ou for enabling MOAC in pkg itself|
-- +==================================================================================+*/

-- -----------------------------------------------------------------------------
-- Procedure for Order Details
-- -----------------------------------------------------------------------------
PROCEDURE XX_order_inv_dtls(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_CUST_NO IN VARCHAR2
);
PROCEDURE XX_order_count_dtls(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_OU IN VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
--P_CUST_NO IN VARCHAR2
P_CUST_NAME VARCHAR2
);
END XX_CUST_DQM_TRX_PKG ;
/
-----------------------------------------------------------------------------------------------------------
*********************************************************************************
PKS ENDS
*********************************************************************************
------------------------------------------------------------------------------------------------------------
create or replace
PACKAGE BODY XX_CUST_DQM_TRX_PKG
AS

/*-- +==================================================================================+
-- | Name : XX_CUST_DQM_TRX_PKG |
-- | |
-- | Created By : NA |
-- | Called From : Concurrent Program |
-- | |
-- | CHANGE HISTORY: |
-- | |
-- |Version Date Name Description |
-- |------- -------- ------------- ------------------------- |
-- |Draft NA NA Initial Draft Version |
-- |1.1 23-Apr-2014 sathish CR#205238 Commented p_ou for enabling MOAC in pkg itself|
-- | added g_org_id variable for enabling moac|
-- +==================================================================================+*/

g_org_id hr_operating_units.organization_id%TYPE := SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10);

-- := fnd_profile.VALUE ('ORG_ID');

--Added g_org_id by sathish for request id#205238 on 09-Apr-2014

PROCEDURE XX_order_inv_dtls (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_CUST_NO IN VARCHAR2)
IS

CURSOR CUR_ORDER_DTLS(P_CUST_NO VARCHAR2) IS
SELECT DISTINCT
ORDER_DTLS.OU,
ORDER_DTLS.PARTY_NAME,
ORDER_DTLS.CUSTOMER_NUMBER,
ORDER_DTLS.ORDER_NUMBER,
ORDER_DTLS.ORDERED_DATE,
ORDER_DTLS.LOCATION,
ORDER_DTLS.SITE_USE_CODE,
ORDER_DTLS.INVNO,
ORDER_DTLS.INVDATE,
ORDER_DTLS.SITNO
FROM
(SELECT HR.NAME OU,
HP.PARTY_NAME,
hzca.account_number CUSTOMER_NUMBER,
ORDER_NUMBER,ordered_date,
location,
hzcst.site_use_code,
INVOICE_DET.TRX_NUMBER INVNO,
INVOICE_DET.TRX_DATE INVDATE,party_site.PARTY_SITE_NUMBER SITNO
FROM hz_cust_accounts hzca,
hz_party_sites party_site ,
hz_locations loc ,
hz_cust_acct_sites_all hzcs,
hz_cust_site_uses_all hzcst,
hz_parties hp,
oe_order_headers_all OEH,
HR_OPERATING_UNITS HR,
(SELECT ct_reference,TRX_NUMBER,TRX_DATE FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET
WHERE hzca.cust_account_id = hzcs.cust_account_id
AND hzcs.cust_acct_site_id =hzcst.cust_acct_site_id
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
AND hzcs.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND hzcs.cust_acct_site_id =hzcst.cust_acct_site_id
AND hp.party_id = hzca.party_id
AND hzcst.ORG_ID = HR.organization_id
AND hzca.cust_account_id =OEH.sold_to_org_id
AND hzcst.site_use_id =OEH.invoice_to_org_id --Invoice to
AND hzcs.status = 'A'
AND hzcst.status = 'A'
AND trunc(ORDERED_DATE) between trunc(sysdate-365) and trunc(sysdate)
UNION
SELECT HR.NAME OU,
HP.PARTY_NAME,
hzca.account_number CUSTOMER_NUMBER,
ORDER_NUMBER,ordered_date,
location,
hzcst.site_use_code,
INVOICE_DET.TRX_NUMBER INVNO,
INVOICE_DET.TRX_DATE INVDATE,party_site.PARTY_SITE_NUMBER SITNO
FROM hz_cust_accounts hzca,
hz_party_sites party_site ,
hz_locations loc ,
hz_cust_acct_sites_all hzcs,
hz_cust_site_uses_all hzcst,
hz_parties hp, oe_order_headers_all OEH,HR_OPERATING_UNITS HR,
(SELECT ct_reference,TRX_NUMBER,TRX_DATE FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET
WHERE hzca.cust_account_id = hzcs.cust_account_id
AND hzcs.cust_acct_site_id =hzcst.cust_acct_site_id
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
AND hzcs.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND hzcs.cust_acct_site_id =hzcst.cust_acct_site_id
AND hp.party_id = hzca.party_id
AND hzcst.ORG_ID = HR.organization_id
AND hzca.cust_account_id =OEH.sold_to_org_id
AND hzcst.site_use_id =OEH.ship_to_org_id --ship to
AND hzcs.status = 'A'
AND hzcst.status = 'A'
AND trunc(ORDERED_DATE) between trunc(sysdate-365) and trunc(sysdate)
ORDER BY 4)
ORDER_DTLS
WHERE ORDER_DTLS.CUSTOMER_NUMBER =NVL(P_CUST_NO,ORDER_DTLS.CUSTOMER_NUMBER);

BEGIN

fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| ' :###XXogic Customer Last One Year Order Details###: '
);

fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| '***********************************************************************************************************************************************************************************************'
);
fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
||RPAD('Operating Unit',20)
||RPAD('Customer Name',50)
||RPAD('Cust No#',13)
||RPAD('Site No#',13)
||RPAD('OrderNo#',13)
||RPAD('Order Date',13)
||RPAD('Location',35)
||RPAD('Site Usage',13)
||RPAD('Invoice#',13)
||RPAD('Inv Date',13)
);

fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| '**********************************************************************************************************************************************************************************************'
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');

FOR crec IN CUR_ORDER_DTLS(P_CUST_NO)
LOOP
fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
||RPAD(crec.ou,20)
||RPAD(crec.PARTY_NAME,50)
||RPAD(crec.CUSTOMER_NUMBER,13)
||RPAD(crec.SITNO,13)
||RPAD(crec.ORDER_NUMBER,13)
||RPAD(crec.ORDERED_DATE,13)
||RPAD(crec.LOCATION,35)
||RPAD(crec.SITE_USE_CODE,13)
||RPAD(crec.INVNO,13)
||RPAD(crec.INVDATE,13)
);

END LOOP;

fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output,
RPAD (' ', 10, ' ') || '***End of Order Details***'
);
END;

PROCEDURE XX_order_count_dtls (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_OU IN VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
-- P_CUST_NO IN VARCHAR2,
P_CUST_NAME VARCHAR2
)
IS

CURSOR CUR_ORDER_DTLS_CNTS(g_org_id hr_operating_units.organization_id%TYPE, --Added by sathish for request id#205238 on 09-Apr-2014
--P_OU VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
--P_CUST_NO VARCHAR2,
P_CUST_NAME VARCHAR2) IS
SELECT TRAN_DTLS.NAME OperatingUnit,
TRAN_DTLS.party_number Party_Number,
TRAN_DTLS.Customer_Name,TRAN_DTLS.Customer_Number,
TRAN_DTLS.party_site_number,TRAN_DTLS.location,
TRAN_DTLS.site_use,
TRAN_DTLS.ORDERNO#,
TRAN_DTLS.INV#,
TRAN_DTLS.address1 ,
TRAN_DTLS.address2 ,
TRAN_DTLS.address3 ,
TRAN_DTLS.address4 ,
TRAN_DTLS.city ,
TRAN_DTLS.state ,
TRAN_DTLS.postal_code ,
TRAN_DTLS.country ,
TRAN_DTLS.organization_id ----Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT DISTINCT CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.party_number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
count(oola.ORDER_NUMBER)ORDERNO#,
count(INVOICE_DET.TRX_NUMBER)INV#,
CUST_DTLS.address1 ,
CUST_DTLS.address2 ,
CUST_DTLS.address3 ,
CUST_DTLS.address4 ,
CUST_DTLS.city ,
CUST_DTLS.state ,
CUST_DTLS.postal_code ,
CUST_DTLS.country ,
CUST_DTLS.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT INVOICE_to_org_id,sold_to_org_id,ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WHERE trunc(ORDERED_DATE) between trunc(sysdate-365) and trunc(sysdate)) OOLA,
(SELECT ct_reference,TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET,
(SELECT distinct
party.party_number,
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number,
party_site.party_site_number,
location,
substrb(look.meaning, 1, 8) Site_Use,
site_uses.site_use_id ,cust.cust_account_id,name,
loc.address1 ,
loc.address2 ,
loc.address3 ,
loc.address4 ,
loc.city ,
loc.state ,
loc.postal_code ,
loc.country ,
hr.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
FROM ar_lookups look ,
ar_lookups look_status ,
hz_cust_accounts_all cust ,
hz_parties party ,
hz_cust_site_uses_all site_uses ,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site ,
hz_locations loc,HR_OPERATING_UNITS HR
WHERE cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = NVL(cust.status, 'A')
AND site_uses.SITE_USE_CODE ='BILL_TO'
AND site_uses.ORG_ID = HR.organization_id
AND site_uses.STATUS = 'A'
AND cust.status = 'A'
) CUST_DTLS
WHERE CUST_DTLS.site_use_id = OOLA.INVOICE_to_org_id(+)
AND CUST_DTLS.cust_account_id = OOLA.sold_to_org_id(+)
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
GROUP BY
CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.party_number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
CUST_DTLS.address1 ,
CUST_DTLS.address2 ,
CUST_DTLS.address3 ,
CUST_DTLS.address4 ,
CUST_DTLS.city ,
CUST_DTLS.state ,
CUST_DTLS.postal_code ,
CUST_DTLS.country ,
CUST_DTLS.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
UNION
SELECT DISTINCT CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.Party_Number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
count(oola.ORDER_NUMBER)ORDERNO#,
count(INVOICE_DET.TRX_NUMBER)INV#,
CUST_DTLS.address1 ,
CUST_DTLS.address2 ,
CUST_DTLS.address3 ,
CUST_DTLS.address4 ,
CUST_DTLS.city ,
CUST_DTLS.state ,
CUST_DTLS.postal_code ,
CUST_DTLS.country ,
CUST_DTLS.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT ship_to_org_id,sold_to_org_id,ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WHERE trunc(ORDERED_DATE) between trunc(sysdate-365) and trunc(sysdate)) OOLA,
(SELECT ct_reference,TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET,
(SELECT distinct
party.party_number,
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number,
party_site.party_site_number,
location,
substrb(look.meaning, 1, 8) Site_Use,
site_uses.site_use_id ,cust.cust_account_id,name,
loc.address1 ,
loc.address2 ,
loc.address3 ,
loc.address4 ,
loc.city ,
loc.state ,
loc.postal_code ,
loc.country ,
hr.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
FROM ar_lookups look ,
ar_lookups look_status ,
hz_cust_accounts_all cust ,
hz_parties party ,
hz_cust_site_uses_all site_uses ,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site ,
hz_locations loc,HR_OPERATING_UNITS HR
WHERE cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = NVL(cust.status, 'A')
AND site_uses.SITE_USE_CODE ='SHIP_TO'
AND site_uses.ORG_ID = HR.organization_id
AND site_uses.STATUS = 'A'
AND cust.status = 'A'
) CUST_DTLS
WHERE CUST_DTLS.site_use_id = OOLA.ship_to_org_id(+)
AND CUST_DTLS.cust_account_id = OOLA.sold_to_org_id(+)
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
GROUP BY
CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.Party_Number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
CUST_DTLS.address1 ,
CUST_DTLS.address2 ,
CUST_DTLS.address3 ,
CUST_DTLS.address4 ,
CUST_DTLS.city ,
CUST_DTLS.state ,
CUST_DTLS.postal_code ,
CUST_DTLS.country ,
CUST_DTLS.organization_id) TRAN_DTLS --Added CUST_DTLS.organization_id by sathish for request id#205238 on 09-Apr-2014
WHERE TRAN_DTLS.CUSTOMER_NAME LIKE NVL(P_CUST_NAME,TRAN_DTLS.CUSTOMER_NAME)
and TRAN_DTLS.organization_id=NVL(g_org_id,TRAN_DTLS.organization_id) --Added by sathish for request id#205238 on 09-Apr-2014
--and TRAN_DTLS.CUSTOMER_NUMBER =NVL(P_CUST_NO,TRAN_DTLS.CUSTOMER_NUMBER)
--AND TRAN_DTLS.NAME in ('US Operating Unit','IE Operating Unit')
--AND TRAN_DTLS.NAME = NVL(P_OU,TRAN_DTLS.NAME) --Commented by sathish for request id#205238 on 09-Apr-2014
order by 4,6;

BEGIN

--Added fnd_file.put_line by sathish for request id#205238 on 09-Apr-2014 for getting operating unit value

fnd_file.put_line (fnd_file.LOG, 'g_org_id ' || g_org_id);

/*
fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| ' :###XXogic Customer Summary Report ###: '
);

fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| '***********************************************************************************************************************************************************************************************************************************************************************************************************************************************************'
); */

fnd_file.put_line (fnd_file.output,
-- RPAD (' ', 3, ' ')
RPAD('Operating Unit',20)
||CHR(9)||RPAD('Party Number',15)
||CHR(9)||RPAD('Customer Name',50)
||CHR(9)||RPAD('Cust No#',13)
||CHR(9)||RPAD('Site No#',13)
||CHR(9)||RPAD('Location#',45)
||CHR(9)||RPAD('Site Usage',13)
||CHR(9)||RPAD('Orders Count#',20)
||CHR(9)||RPAD('Invoice Count#',20)
||CHR(9)||RPAD('Address1',50)
||CHR(9)||RPAD('Address2',50)
||CHR(9)||RPAD('Address3',50)
||CHR(9)||RPAD('Address4',50)
||CHR(9)||RPAD('City',35)
||CHR(9)||RPAD('Zip Code',15)
||CHR(9)||RPAD('State',25)
||CHR(9)||RPAD('Country',10)
);

/* fnd_file.put_line (fnd_file.output,
RPAD (' ', 3, ' ')
|| '***********************************************************************************************************************************************************************************************************************************************************************************************************************************************************'
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');*/

FOR crec IN CUR_ORDER_DTLS_CNTS(g_org_id, --Added by sathish for request id#205238 on 09-Apr-2014
--P_OU, --Commented by sathish for request id#205238 on 09-Apr-2014
--P_CUST_NO,
P_CUST_NAME)
LOOP
fnd_file.put_line (fnd_file.output,
--RPAD (' ', 3, ' ')
RPAD(crec.OperatingUnit,20)
||CHR(9)||RPAD(crec.Party_Number,15)
||CHR(9)||RPAD(crec.Customer_Name,50)
||CHR(9)||RPAD(crec.CUSTOMER_NUMBER,13)
||CHR(9)||RPAD(crec.party_site_number,13)
||CHR(9)||RPAD(crec.LOCATION,45)
||CHR(9)||RPAD(crec.site_use,13)
||CHR(9)||RPAD(crec.ORDERNO#,20)
||CHR(9)||RPAD(crec.INV#,20)
||CHR(9)||RPAD(nvl(crec.address1,' '),50)
||CHR(9)||RPAD(nvl(crec.address2,' '),50)
||CHR(9)||RPAD(nvl(crec.address3,' '),50)
||CHR(9)||RPAD(nvl(crec.address4,' '),50)
||CHR(9)||RPAD(nvl(crec.City,' '),35)
||CHR(9)||RPAD(nvl(crec.postal_code,' '),15)
||CHR(9)||RPAD(nvl(crec.State,' '),25)
||CHR(9)||RPAD(crec.country,10)
);

END LOOP;
/*
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output,
RPAD (' ', 10, ' ') || '***End of Order Details***'
);*/
END;

END XX_CUST_DQM_TRX_PKG;
/

E-Business Suite

Wednesday, 27 August 2014

Enabling multiple org through back end by package in oracle apps R12.1.3 Enclosed the pkb and pks query here

No comments:

Post a Comment