Oracle Apps Technical

Thursday, 13 June 2013

Query for Ledger, OU, Legal Enity

Query for Ledger, OU, Legal Enity, balancing segment:

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units   tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

Sunday, 4 November 2012

API to get Customer Credit Exposure Amount in Oracle Apps

DECLARE
V_HOLD_ID NUMBER DEFAULT 50;
L_CUST_TOTAL_EXPOSURE NUMBER;
P_CREDIT_CHECK_RULE_ID NUMBER;
V_MSG_DATA VARCHAR2 (4000);
L_RETURN_STATUS VARCHAR2 (30);
BEGIN
MO_GLOBAL.INIT ('ONT');
FND_GLOBAL.APPS_INITIALIZE (USER_ID => 1110,
RESP_ID => 50678,
RESP_APPL_ID => 7000
);
MO_GLOBAL.SET_POLICY_CONTEXT ('S', 83);

DBMS_OUTPUT.PUT_LINE ('Calling the API to get customr exposure amount');

OE_CREDIT_ENGINE_GRP.GET_CUSTOMER_EXPOSURE
(P_CUSTOMER_ID => 1590, ---- Customer Account ID
P_SITE_ID => NULL, ---- Customer Site ID
P_LIMIT_CURR_CODE => 'INR', ---- Currency Code
P_CREDIT_CHECK_RULE_ID => 1041, ---- Credit_Check_Rule_ID from table OE_CREDIT_CHECK_RULES
X_TOTAL_EXPOSURE => L_CUST_TOTAL_EXPOSURE,
X_RETURN_STATUS => L_RETURN_STATUS
);

IF L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.PUT_LINE ('success:');
DBMS_OUTPUT.PUT_LINE ( 'l_total_exposure_amount - '
|| L_CUST_TOTAL_EXPOSURE
);
COMMIT;
ELSIF L_RETURN_STATUS IS NULL
THEN
DBMS_OUTPUT.PUT_LINE ('Status is null');
ELSE
DBMS_OUTPUT.PUT_LINE ('Failed: ' || V_MSG_DATA);

FOR I IN 1 .. OE_MSG_PUB.COUNT_MSG
LOOP
V_MSG_DATA := OE_MSG_PUB.GET (P_MSG_INDEX => I, P_ENCODED => 'F');
DBMS_OUTPUT.PUT_LINE (I || ') ' || V_MSG_DATA);
END LOOP;

ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error is ' || SQLCODE || '---' || SQLERRM);
END;

Query to get Year Calendar

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
"Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sun",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mon",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tue",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Wed",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Thu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fri",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sat"
FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <=
ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
- TRUNC (SYSDATE, 'y'))
GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

Tuesday, 28 August 2012

API to Create Item Cost in OPM

GMF_ITEMCOST_PUB.CREATE_ITEM_COST API is used to create item cost in Oracle Process Manufacturing.
This API creates a new item cost in the Cost Details table. [ Table = CM_CMPT_DTL]

The GMF_ITEMCOST_PUB package defines and uses the following custom PL/SQL
Record Types and INDEX BY table Types:
TYPE header_rec_type IS RECORD
(
period_id cm_cmpt_dtl.period_id%TYPE,
calendar_code cm_cldr_hdr_b.calendar_code%TYPE,
period_code cm_cldr_dtl.period_code%TYPE,
cost_type_id cm_cmpt_dtl.cost_type_id%TYPE,
cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE,
organization_id cm_cmpt_dtl.organization_id%TYPE,
organization_code mtl_parameters.organization_code%TYPE,
inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
item_number mtl_item_flexfields.item_number%TYPE,
user_name fnd_user.user_name%TYPE
);

TYPE this_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
burden_ind NUMBER,
total_qty NUMBER,
costcalc_orig NUMBER,
rmcalc_type NUMBER,
delete_mark NUMBER,
attribute1 cm_cmpt_dtl.attribute1%TYPE,
attribute2 cm_cmpt_dtl.attribute2%TYPE,
attribute3 cm_cmpt_dtl.attribute3%TYPE,
attribute4 cm_cmpt_dtl.attribute4%TYPE,
attribute5 cm_cmpt_dtl.attribute5%TYPE,
attribute6 cm_cmpt_dtl.attribute6%TYPE,
attribute7 cm_cmpt_dtl.attribute7%TYPE,
attribute8 cm_cmpt_dtl.attribute8%TYPE,
attribute9 cm_cmpt_dtl.attribute9%TYPE,
attribute10 cm_cmpt_dtl.attribute10%TYPE,
attribute11 cm_cmpt_dtl.attribute11%TYPE,
attribute12 cm_cmpt_dtl.attribute12%TYPE,
attribute13 cm_cmpt_dtl.attribute13%TYPE,
attribute14 cm_cmpt_dtl.attribute14%TYPE,
attribute15 cm_cmpt_dtl.attribute15%TYPE,
attribute16 cm_cmpt_dtl.attribute16%TYPE,
attribute17 cm_cmpt_dtl.attribute17%TYPE,
attribute18 cm_cmpt_dtl.attribute18%TYPE,
attribute19 cm_cmpt_dtl.attribute19%TYPE,
attribute20 cm_cmpt_dtl.attribute20%TYPE,
attribute21 cm_cmpt_dtl.attribute21%TYPE,
attribute22 cm_cmpt_dtl.attribute22%TYPE,
attribute23 cm_cmpt_dtl.attribute23%TYPE,
attribute24 cm_cmpt_dtl.attribute24%TYPE,
attribute25 cm_cmpt_dtl.attribute25%TYPE,
attribute26 cm_cmpt_dtl.attribute26%TYPE,
attribute27 cm_cmpt_dtl.attribute27%TYPE,
attribute28 cm_cmpt_dtl.attribute28%TYPE,
attribute29 cm_cmpt_dtl.attribute29%TYPE,
attribute30 cm_cmpt_dtl.attribute30%TYPE,
attribute_category cm_cmpt_dtl.attribute_category%TYPE
);

TYPE this_level_dtl_tbl_type IS TABLE OF this_level_dtl_rec_type INDEX
BY BINARY_INTEGER;
TYPE lower_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
delete_mark NUMBER
);
TYPE lower_level_dtl_tbl_type IS TABLE OF lower_level_dtl_rec_type
INDEX BY BINARY_INTEGER;

TYPE costcmpnt_ids_rec_type IS RECORD
(
cost_cmpntcls_id NUMBER,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cost_level NUMBER,
cmpntcost_id NUMBER
);

TYPE costcmpnt_ids_tbl_type IS TABLE OF costcmpnt_ids_rec_type INDEX
BY BINARY_INTEGER;

PL - SQL Code for API:-

ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

DECLARE
L_STATUS VARCHAR2 (11);
L_RETURN_STATUS VARCHAR2 (11) := FND_API.G_RET_STS_SUCCESS;
L_COUNT NUMBER (10);
L_RECORD_COUNT NUMBER (10) := 0;
L_LOOP_CNT NUMBER (10) := 0;
L_DUMMY_CNT NUMBER (10) := 0;
L_DATA VARCHAR2 (3000);
L_MSG_INDEX_OUT NUMBER;
L_HEADER_REC GMF_ITEMCOST_PUB.HEADER_REC_TYPE;
L_THIS_LVL_TBL GMF_ITEMCOST_PUB.THIS_LEVEL_DTL_TBL_TYPE;
L_LOWER_LVL_TBL GMF_ITEMCOST_PUB.LOWER_LEVEL_DTL_TBL_TYPE;
L_COSTCMPNT_IDS GMF_ITEMCOST_PUB.COSTCMPNT_IDS_TBL_TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID => 1110,
RESP_ID => 50687,
RESP_APPL_ID => 555
);

L_HEADER_REC.PERIOD_ID := 141;
L_HEADER_REC.CALENDAR_CODE := 'CST_CAL';
L_HEADER_REC.PERIOD_CODE := 'JUN 2012'; --NULL;
L_HEADER_REC.COST_TYPE_ID := 1000;
L_HEADER_REC.COST_MTHD_CODE := 'PMAC'; -- NULL;
L_HEADER_REC.ORGANIZATION_ID := 229;
L_HEADER_REC.ORGANIZATION_CODE := 'R02'; --NULL;
L_HEADER_REC.INVENTORY_ITEM_ID := 2005;
--L_HEADER_REC.ITEM_NUMBER := NULL;
L_HEADER_REC.USER_NAME := 'USER1';

L_THIS_LVL_TBL (1).CMPNTCOST_ID := 21188;
L_THIS_LVL_TBL (1).COST_CMPNTCLS_ID := 1;
L_THIS_LVL_TBL (1).COST_CMPNTCLS_CODE := 'MATERIAL';
L_THIS_LVL_TBL (1).COST_ANALYSIS_CODE := 'DIR';
L_THIS_LVL_TBL (1).CMPNT_COST := 60;
L_THIS_LVL_TBL (1).BURDEN_IND := 0;
L_THIS_LVL_TBL (1).TOTAL_QTY := 0;
L_THIS_LVL_TBL (1).COSTCALC_ORIG := 1;
L_THIS_LVL_TBL (1).RMCALC_TYPE := 1;
L_THIS_LVL_TBL (1).DELETE_MARK := 0;

DBMS_OUTPUT.PUT_LINE ('Start');
GMF_ITEMCOST_PUB.CREATE_ITEM_COST
(P_API_VERSION => 3.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
X_RETURN_STATUS => L_STATUS,
X_MSG_COUNT => L_COUNT,
X_MSG_DATA => L_DATA,
P_HEADER_REC => L_HEADER_REC,
P_THIS_LEVEL_DTL_TBL => L_THIS_LVL_TBL,
P_LOWER_LEVEL_DTL_TBL => L_LOWER_LVL_TBL,
X_COSTCMPNT_IDS => L_COSTCMPNT_IDS
);
DBMS_OUTPUT.PUT_LINE ('Status:' || L_STATUS);
DBMS_OUTPUT.PUT_LINE ('Debug:' || L_COUNT);

IF L_STATUS = 'S'
THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE ('success!!');
ELSE
IF L_COUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE ('Error 11111 ' || L_DATA);
ELSE
DBMS_OUTPUT.PUT_LINE ( 'status: '
|| L_STATUS
|| ' Error Count '
|| L_COUNT
);

FOR I IN 1 .. L_COUNT
LOOP
FND_MSG_PUB.GET (P_MSG_INDEX => I,
P_DATA => L_DATA,
P_ENCODED => FND_API.G_FALSE,
P_MSG_INDEX_OUT => L_MSG_INDEX_OUT
);
DBMS_OUTPUT.PUT_LINE ('Error 2222: ' || SUBSTR (L_DATA, 1, 255));
END LOOP;
END IF;
END IF;

DBMS_OUTPUT.PUT_LINE ('end of procedure');
END;

API for Bank Account Creation in Oracle Apps

CE_BANK_PUB.CREATE_BANK_ACCT API is used to create a bank account for the branch party id passed as an input parameter. The API uses the BANKACCT_REC_TYPE record to pass the input values. On successful creation of bank account, the API returns the bank account id along with the information /error messages. The API returns a null bank account id if the bank branch is not created.

DECLARE

p_init_msg_list VARCHAR2 (200);
p_acct_rec apps.ce_bank_pub.bankacct_rec_type;
x_acct_id NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_count NUMBER;

BEGIN
p_init_msg_list := NULL;
-- HZ_PARTIES.PARTY_ID BANK BRANCH
p_acct_rec.branch_id := 8056;
-- HZ_PARTIES.PARTY_ID BANK
p_acct_rec.bank_id := 8042;
-- HZ_PARTIES.PARTY_ID ORGANIZATION
p_acct_rec.account_owner_org_id := 23273;
-- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
p_acct_rec.account_owner_party_id := 2041;

p_acct_rec.account_classification := 'INTERNAL';
p_acct_rec.bank_account_name := 'Test Bank Accunt';
p_acct_rec.bank_account_num := 14256789;
p_acct_rec.currency := 'INR';
p_acct_rec.start_date := SYSDATE;
p_acct_rec.end_date := NULL;

CE_BANK_PUB.CREATE_BANK_ACCT
(p_init_msg_list => p_init_msg_list,
p_acct_rec => p_acct_rec,
x_acct_id => x_acct_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

IF x_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
ELSIF x_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
END LOOP;
END IF;
END;

Saturday, 9 June 2012

Open Concurrent Request Output File and Log file on button click in Custom Form in Oracle Apps R12

In order to open concurrent request output or log file in browser by clicking on button in Custom Oracle form we need to do the following steps to achieve this:

1] Open custom form in Form Builder and click on Attached Libraries. Then click on + add button.

2] Add FNDCONC.pll library file. This library file is available in the $AU_TOP/../resource folder.

3] Now open the FNDCONC.pll library file from Resource folder in Oracle Form Builder. Inside FNDCONC.pll library there are standard program units defined by oracle.

4] In order to open submitted request output file by clicking on Button we need to use procedure report defined in EDITOR_PKG.

Syntax - EDITOR_PKG.report (req_id number, save_output_flag varchar2);
where, req_id is request id of submitted request
save_output_flag is to indicate whether output file to be saved or not.

5] To open submitted request log file by clicking on Button we need to use procedure request_log defined in EDITOR_PKG.

Syntax - EDITOR_PKG .request_log(req_id number);
where, req_id is request id of submitted request.

6] Now call the above two procedures on button’s WHEN-BUTTON-PRESSED trigger to open concurrent request log/output file.

7] Save and compile the form on server, and check the request output and log file by clicking button on custom form.

Friday, 8 June 2012

Supplier Interface in Oracle Apps R12

There are three interface tables in oracle apps Supplier Interface.

1] AP_SUPPILERS_INT
2] AP_SUPPLIER_SITES_INT
3] AP_SUP_SITE_CONTACTS_INT

Supplier Interface Programs:

1] Supplier Open Interface Import
2] Supplier Sites Open Interface Import
3] Supplier Site Contacts Open Interface Import

1] AP_SUPPLIERS_INT

This is the Open interface table for Supplier used to store Supplier header information. The columns mapped to corresponding AP_SUPPLIERS table.

‘Supplier Open Interface Import’ request is used to import Supplier Header Information into Oracle.

Mandatory Columns:
VENDOR_INTERFACE_ID è AP_SUPPLIERS_INT_S.NEXTVAL [ Unique Identifier]
VENDOR_NAME è Supplier Name

Other Important Columns:
            VENDOR_TYPE_LOOKUP_CODE è Supplier Type [e.g. Company etc]
            SUMMARY_FLAG                           è Key Flexfield Summary Flag
            ENABLED_FLAG                            è Key Flexfield Enable flag
            NUM_1099                                         è Tax identification number
            INVOICE_CURRENCY_CODE     è Invoice Currency Identifier
            TERMS_ID                                        è Payment Terms Identifier
            SEGMENT1                                      è Vendor Number

2] AP_SUPPLIER_SITES_INT

This is the Open interface table to import supplier sites information. The columns mapped to corresponding AP_SUPPLIER_SITES_ALL table.

‘Supplier Sites Open Interface Import’ request is used to import Supplier site Information into Oracle.

Mandatory Columns:
            VENDOR_SITE_INTERFACE_ID   è AP_SUPPLIER_SITES_INT_S.NEXTVAL [Unique Identifier]
            VENDOR_SITE_CODE                  è Supplier Site Name
            VENDOR_ID                                               è Vendor ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.

Other Important Columns:
            ADDRESS_LINE1                è First Line of Address
            ADDRESS_LINE2                è Second Line of Address
            ADDRESS_LINE3                è Third line of Address
            CITY                                       è City Name
            STATE                                   è State Name
            COUNTRY                            è Country Name
            ZIP                                          è Postal Code
            PHONE                                  è Phone Number
            FAX                                        è Fax Number
            EMAIL_ADDRESS              è Supplier Site URL
            PURCHASING_SITE_FLAGè To indicate purchasing allow from this site [Y/N]
            RFQ_ONLY_SITE_FLAG   è To indicate quotations allow from this site [Y/N]
            PAY_SITE_FLAG                è To indicate payable invoice allow for this site [Y/N]
            INVOICE_CURRENCY_CODEè Invoice Currency Identifier
            TERMS_ID                            è Payment Terms ID

3] AP_SUP_SITE_CONTACT_INT:

This is Open interface table for supplier site contacts. The columns mapped to corresponding AP_SUPPLIER_CONTACTS table.

Supplier Site Contacts Open Interface Import’ request is used to import Supplier site Contacts Information into Oracle.

Mandatory Columns:
VENDOR_CONTACT_INTERFACE_ID è AP_SUP_SITE_CONTACT_INT_S.NEXTVAL [Unique Identifier]
VENDOR_SITE_CODE è Supplier Site Code

Other Important Columns:
            VENDOR_ID                                        è Vendor_ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.
            VENDOR_SITE_ID                 è Vendor_Site_ID created in AP_SUPPLIER_SITES_ALL table after submission of Supplier Sites Open Interface Import request.
            PREFIX                                     è Title e.g. [Mr, Mrs, Doctor etc]
            FIRST_NAME                          è Contact First Name
            MIDDLE_NAME                     è Contact Middle Name
            LAST_NAME                                        è Contact Last Name
            AREA_CODE                                        è Area Code of Contact phone number
            PHONE                                      è Contact phone number
            FAX_AREA_CODE                  è Area Code of Contact facsimile number
            FAX                                            è Contact facsimile number

Validation during Supplier Import using interface:

1] AP_SUPPLIER_INT.VENDOR_NAME
Validation for duplicate Supplier Name in AP_SUPPLIERS table.

2] AP_SUPPLIER_INT.VENDOR_TYPE_LOOKUP_CODE
Validations for valid Supplier Type in PO_LOOKUP_CODES table for LOOK_TYPE as ‘VENDOR TYPE’.

3] AP_SUPPLIER_INT.SEGMENT1
Validation for duplicate Supplier Number in AP_SUPPLIERS table.

4] AP_SUPPLIER_SITES_INT.VENDOR_SITE_CODE
Validation for duplicate Supplier Site Name in AP_SUPPLIER_SITES table.

5] AP_SUPPLIER_SITES_INT.COUNTRY
Validation for country code in FND_TERRITORIES_VL table.

AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.