Value set(DFF/KFF) Upload with Qualifier and child level data

DECLARE
   l_enabled_flag         VARCHAR2 (2);
   l_summary_flag         VARCHAR2 (2);
   l_who_type             fnd_flex_loader_apis.who_type;
   l_user_id              NUMBER := fnd_global.user_id;
   l_login_id             NUMBER := fnd_global.login_id;
   l_value_set_name       fnd_flex_value_sets.flex_value_set_name%TYPE;
   l_value_set_value      fnd_flex_values.flex_value%TYPE;
   l_flex_value_meaning   VARCHAR2 (250);
   l_description          VARCHAR2 (250);
   l_child_low            VARCHAR2 (240);
   l_child_high           VARCHAR2 (240);
   l_posting_allowed      VARCHAR2 (1);
   l_budget_allowed       VARCHAR2 (1);
BEGIN
   l_value_set_name := 'XXXXXXX';
   l_value_set_value := '111';
   l_flex_value_meaning := '111';
   l_description := 'Test1';
   l_child_low := '123';
   l_child_high := '124';
   l_enabled_flag := 'Y';
   l_summary_flag := 'Y';                                    -- enables parent
   l_budget_allowed := 'Y';                                      --QUALIFIER 1
   l_posting_allowed := 'Y';                                     --QUALIFIER 2
   l_who_type.created_by := l_user_id;
   l_who_type.creation_date := SYSDATE;
   l_who_type.last_updated_by := l_user_id;
   l_who_type.last_update_date := SYSDATE;
   l_who_type.last_update_login := l_login_id;

   fnd_flex_loader_apis.
   up_value_set_value (
      p_upload_phase                 => 'BEGIN',
      p_upload_mode                  => NULL,
      p_custom_mode                  => 'FORCE',
      p_flex_value_set_name          => l_value_set_name,
      p_parent_flex_value_low        => NULL,
      p_flex_value                   => l_value_set_value,
      p_owner                        => NULL,
      p_last_update_date             => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
      p_enabled_flag                 => l_enabled_flag,
      p_summary_flag                 => l_summary_flag,
      p_start_date_active            => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
      p_end_date_active              => NULL,
      p_parent_flex_value_high       => NULL,
      p_rollup_flex_value_set_name   => NULL,
      p_rollup_hierarchy_code        => NULL,
      p_hierarchy_level              => NULL,
      p_compiled_value_attributes    => 'NN',
      p_value_category               => NULL,
      p_attribute1                   => NULL,
      p_attribute2                   => NULL,
      p_attribute3                   => NULL,
      p_attribute4                   => NULL,
      p_attribute5                   => NULL,
      p_attribute6                   => NULL,
      p_attribute7                   => NULL,
      p_attribute8                   => NULL,
      p_attribute9                   => NULL,
      p_attribute10                  => NULL,
      p_attribute11                  => NULL,
      p_attribute12                  => NULL,
      p_attribute13                  => NULL,
      p_attribute14                  => NULL,
      p_attribute15                  => NULL,
      p_attribute16                  => NULL,
      p_attribute17                  => NULL,
      p_attribute18                  => NULL,
      p_attribute19                  => NULL,
      p_attribute20                  => NULL,
      p_attribute21                  => NULL,
      p_attribute22                  => NULL,
      p_attribute23                  => NULL,
      p_attribute24                  => NULL,
      p_attribute25                  => NULL,
      p_attribute26                  => NULL,
      p_attribute27                  => NULL,
      p_attribute28                  => NULL,
      p_attribute29                  => NULL,
      p_attribute30                  => NULL,
      p_attribute31                  => NULL,
      p_attribute32                  => NULL,
      p_attribute33                  => NULL,
      p_attribute34                  => NULL,
      p_attribute35                  => NULL,
      p_attribute36                  => NULL,
      p_attribute37                  => NULL,
      p_attribute38                  => NULL,
      p_attribute39                  => NULL,
      p_attribute40                  => NULL,
      p_attribute41                  => NULL,
      p_attribute42                  => NULL,
      p_attribute43                  => NULL,
      p_attribute44                  => NULL,
      p_attribute45                  => NULL,
      p_attribute46                  => NULL,
      p_attribute47                  => NULL,
      p_attribute48                  => NULL,
      p_attribute49                  => NULL,
      p_attribute50                  => NULL,
      p_flex_value_meaning           => l_flex_value_meaning,
      p_description                  => l_description);

   fnd_flex_loader_apis.
   up_val_qual_value (
      p_upload_phase                   => 'BEGIN',
      p_upload_mode                    => NULL,
      p_custom_mode                    => 'FORCE',
      p_flex_value_set_name            => l_value_set_name,
      p_parent_flex_value_low          => NULL,                             --
      p_flex_value                     => l_value_set_value,
      p_id_flex_application_short_na   => 'SQLGL',
      p_id_flex_code                   => 'GL#',
      p_segment_attribute_type         => 'GL_GLOBAL',
      p_value_attribute_type           => 'DETAIL_BUDGETING_ALLOWED',
      p_owner                          => NULL,
      p_last_update_date               => TO_CHAR (SYSDATE,
                                                   'YYYY/MM/DD HH24:MI:SS'),
      p_assignment_order               => NULL,
      p_assignment_date                => NULL,
      p_compiled_value_attribute_val   => l_budget_allowed);

   fnd_flex_loader_apis.
   up_val_qual_value (
      p_upload_phase                   => 'BEGIN',
      p_upload_mode                    => NULL,
      p_custom_mode                    => 'FORCE',
      p_flex_value_set_name            => l_value_set_name,
      p_parent_flex_value_low          => NULL,
      p_flex_value                     => l_value_set_value,
      p_id_flex_application_short_na   => 'SQLGL',
      p_id_flex_code                   => 'GL#',
      p_segment_attribute_type         => 'GL_GLOBAL',
      p_value_attribute_type           => 'DETAIL_POSTING_ALLOWED',
      p_owner                          => NULL,
      p_last_update_date               => TO_CHAR (SYSDATE,
                                                   'YYYY/MM/DD HH24:MI:SS'),
      p_assignment_order               => NULL,
      p_assignment_date                => NULL,
      p_compiled_value_attribute_val   => l_posting_allowed);

   IF l_summary_flag = 'Y'
   THEN
      fnd_flex_loader_apis.
      up_val_norm_hierarchy (
         p_upload_phase            => 'BEGIN',
         p_upload_mode             => NULL,
         p_custom_mode             => 'FORCE',
         p_flex_value_set_name     => l_value_set_name,
         p_parent_flex_value       => l_value_set_value,
         p_range_attribute         => 'C',
         p_child_flex_value_low    => l_child_low,
         p_child_flex_value_high   => l_child_high,
         p_owner                   => NULL,
         p_last_update_date        => TO_CHAR (SYSDATE,
                                               'YYYY/MM/DD HH24:MI:SS'),
         p_start_date_active       => TO_CHAR (SYSDATE,
                                               'YYYY/MM/DD HH24:MI:SS'),
         p_end_date_active         => NULL);
   END IF;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));
END;

Registering a custom table in Oracle Apps.

CREATE TABLE emp_details

(

   eno                  NUMBER (6) CONSTRAINT eno_pk PRIMARY KEY,

   ename                VARCHAR2 (15) NOT NULL,

   dept                 VARCHAR2 (15),

   jdate                DATE,

   salary               NUMBER,

   created_by           NUMBER (15),

   creation_date        DATE,

   last_updated_by      NUMBER (15),

   last_update_date     DATE,

   Last_update_login    VARCHAR2 (50),

   attribute1           VARCHAR2 (50),

   attribute2           VARCHAR2 (50),

   attribute3           VARCHAR2 (50),

   attribute4           VARCHAR2 (50),

   attribute5           VARCHAR2 (50),

   attribute_category   VARCHAR2 (100)

);

Grant all on emp_details to Apps;

COMMIT;

EXECUTE AD_DD.Register_Table('SQLGL','emp_details','T',8,10,90); 

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','empno',1,'Number',6,'N','Y'); 

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','ename',2,'Varchar2',15,'N','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','dept',3,'Varchar2',15,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','jdate',4,'Date',11,'Y','Y'); 

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','salary',5,'Number',38,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','created_by',6,'Number',15,'Y','Y'); 

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','creation_date',7,'Date',11,'Y','Y'); 

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_updated_by',8,'Number',15,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_update_date',9,'Date',11,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute1',10,'Varchar2',50,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute2',11,'Varchar2',50,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute3',12,'Varchar2',50,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute4',13,'Varchar2',50,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute5',14,'Varchar2',50,'Y','Y');  

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute_category',15,'Varchar2',100,'Y','Y'); 

 

COMMIT;

SELECT * FROM emp_details;

for checking in front end

go to appication developer >> applications >> database >> TABLE

Oracle Purchasing Tables & API

Usual Oracle Applications PO Tables
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: PO Distribution where the accounts are
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_LINES_TYPES_TL: PO Line Types used in MLS
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_ALL_V: Buyer
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts
PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers
PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path


OTHER RELATED: 
MRP_SOURCING_RULES: Used for Auto-Sourcing Rules
MRP_SR_RECEIPT_ORG: Used for Auto-Sourcing Rules
MRP_SR_SOURCE_ORG: Used for Auto-Sourcing Rules
MRP_ASSIGNMENT_SETS: Used for Auto-Sourcing Rules
MRP_SR_ASSIGNMENTS: Used for Auto-Sourcing Rules
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received

INTERFACES:
PO_HEADERS_INTERFACE: Used for creating PO
PO_LINES_INTERFACE: Used for creating PO
PO_DISTRIBUTIONS_INTERFACE: Used for creating PO
PO_REQUISITIONS_INTERFACE: Used for creating Requisition
PO_REQ_DIST_INTERFACE: Used for creating Requisition
PO_INTERFACE_ERRORS: Error created during the processing of the PO or requisition
RCV_INTERFACE: Used for creating Received Items
RCV_HEADERS_INTERFACE: Used for creating Received Group of items
RCV_LOT_INTERFACE: Used for receiving item into a particular lot

API:
Following API can be used to modify
a) Need by Date
b) Promise Date
c) Quantity
d) Price

       po_change_api1_s.update_po
                                 (x_po_number                => c1_rec.po_number
                                 ,x_release_number           => NULL
                                 ,x_revision_number          => v_REVISION_NUM
                                 ,x_line_number              => c1_rec.line_num
                                 ,x_shipment_number          => NULL
                                 ,new_quantity               => NULL
                                 ,new_price                  => c1_rec.new_price
                                 ,new_promised_date          => NULL
                                 ,new_need_by_date           => NULL
                                 ,launch_approvals_flag      => l_chr_lch_appr_flg
                                 ,update_source              => NULL
                                 ,VERSION                    => '1.0'
                                 ,x_override_date            => NULL
                                 ,x_api_errors               => l_rec_out_error
                                 ,p_buyer_name               => c1_rec.buyer
                                 ,p_secondary_quantity       => NULL
                                 ,p_preferred_grade          => NULL
                                 ,p_org_id                   => c1_rec.org_id
                                 );

2) Following is to call the PO workflow.

 po_reqapproval_init1.
          start_wf_process (itemtype                 => 'POAPPRV',
                            itemkey                  => l_num_item_key,
                            workflowprocess          => '<<<<>>>>>>>>>',
                            actionoriginatedfrom     => 'PO_FORM',
                            documentid               => i.po_header_id,
                            documentnumber           => i.segment1,
                            preparerid               => i.agent_id,
                            documenttypecode         => 'PO',
                            documentsubtype          => 'STANDARD',
                            submitteraction          => 'APPROVE',
                            forwardtoid              => NULL,
                            forwardfromid            => NULL,
                            defaultapprovalpathid    => NULL,
                            note                     => NULL,
                            printflag                => 'N',
                            faxflag                  => 'N',
                            faxnumber                => NULL,
                            emailflag                => 'N',
                            emailaddress             => NULL,
                            createsourcingrule       => 'N',
                            releasegenmethod         => 'N',
                            updatesourcingrule       => 'N',
                            massupdatereleases       => 'N',
                            retroactivepricechange   => 'N',
                            orgassignchange          => 'N',
                            communicatepricechange   => 'N',
                            p_background_flag        => 'N',
                            p_initiator              => NULL,
                            p_xml_flag               => NULL,
                            fpdsngflag               => 'N',
                            p_source_type_code       => NULL);

Debugging the Pick - Pack -Shipping / OM related debugging

 A To generate a debug file from the Shipping Transaction or Quick Ship forms in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
WSH: Debug Log Directory - any directory that can be written to by the database

To check, run the following SQL statement:
Code:
select value from v$parameter where name = 'utl_file_dir'
Set profile option WSH: Debug Log Directory at the Site & Application Level.
In the Shipping form go to Tools and check the Debug box. This will print out a file name - NOTE down this file name.
Perform the action you wish to debug.
Go to Tools and uncheck Debug.
After you have completed generating the debug file, please set the value of the profile OM: Debug Level back to 0 and WSH: Debug Enabled set to No, otherwise there will be some performance impact. Retrieve the debug file from the directory specified in step 1.

B. To generate debug information for Pick Release in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
In the Release Sales Order for Picking form go to Tools and check the Debug box.
Submit the pick release.
Provide the Pick Selection List Generation log file.

C. To generate debug information for Pick Release prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
In the Release Sales Order for Picking form go to Tools and check the Debug box.
Submit the pick release.
Provide the Pick Selection List Generation log file.


D. To generate debug information for Interface Trip Stop - SRS in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
Set the Debug Level parameter to 1 (Debugging ON) .
Submit the job.
Provide the log file.

E. To generate debug information for Interface Trip Stop - SRS prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
Set the Log Level parameter to 1 (Debugging ON) .
Submit the job.
Provide the log file.

F. To generate debug information for an API in 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
WSH: Debug Log Directory - set to a valid writeable directory path
Add the following line of code in the wrapper script which calls the API:
Code:
DECLARE l_file_name VARCHAR2(32767);
l_return_status VARCHAR2(32767);
l_msg_data VARCHAR2(32767);
l_msg_count NUMBER;
BEGIN
fnd_profile.put('WSH_DEBUG_MODULE','%');
fnd_profile.put('WSH_DEBUG_LEVEL',WSH_DEBUG_SV.C_STMT_LEVEL);
wsh_debug_sv.start_debugger(l_file_name,l_return_status,l_msg_data,l_msg_count);
Submit the job.
Provide the log file.

G. To generate debug information for an API prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
Add the following lines of code in the wrapper script which calls the API:
Code:
oe_debug_pub.initialize; oe_debug_pub.SetDebugLevel(5);
DBMS_OUTPUT.PUT_LINE('Debug File = ' ||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

On Hand Qty Query

SELECT SUM (target_qty), item_id, subinv
FROM (SELECT moqv.subinventory_code subinv
,moqv.inventory_item_id item_id
,SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
AND moqv.subinventory_code =
NVL (:subinventory_code, moqv.subinventory_code)
GROUP BY moqv.subinventory_code
,moqv.inventory_item_id
,moqv.item_cost
UNION ALL
SELECT mmt.subinventory_code subinv
,mmt.inventory_item_id item_id
,-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >=
NVL (TO_DATE (:hist_date), TRUNC (SYSDATE))
+ 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
AND mmt.subinventory_code =
NVL (:subinventory_code, mmt.subinventory_code)
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id,subinv

FINALLY CLOSE -- A PO /PO Line

Following API Can be used to "FINALLY CLOSE" a Standard/Blanket PO at header/line level.

• Given example is at Line level for blanket PO.
• For header level PO close, P_lineid will be passed as null.
• For standard PO , instead of PA use PO.
• If blanket PO line is referred on any OPEN standard PO, then BPA line cannot be changed to FINALLY CLOSE status.

**************************************************

DECLARE
x_action CONSTANT VARCHAR2 (20) := 'FINALLY CLOSE';
-- Change this parameter as per requirement
x_calling_mode CONSTANT VARCHAR2 (2) := 'PO';
x_conc_flag CONSTANT VARCHAR2 (1) := 'N';
x_return_code_h VARCHAR2 (100);
x_auto_close CONSTANT VARCHAR2 (1) := 'N';
x_origin_doc_id NUMBER;
x_returned BOOLEAN;

CURSOR c_po_details
IS
SELECT pha.po_header_id, pha.org_id, pha.segment1, pha.agent_id
,pha.closed_code, pha.closed_date
FROM apps.po_headers_all pha
WHERE authorization_status = 'APPROVED'
AND pha.closed_code <> 'FINALLY CLOSED'
AND segment1 = '15000002252';
-- Enter the PO Number if one PO needs to be finally closed/Closed
BEGIN
fnd_global.apps_initialize (user_id => 1468
,resp_id => 20707
,resp_appl_id => 201
);

FOR po_head IN c_po_details
LOOP
mo_global.init ('PA');
mo_global.set_policy_context ('S', po_head.org_id);
DBMS_OUTPUT.put_line
( 'Calling PO_Actions.close_po for Closing/Finally Closing PO =>'
|| po_head.segment1
);
x_returned :=
po_actions.close_po (p_docid => po_head.po_header_id
,p_doctyp => 'PA' -- "PO" for standard PO
,p_docsubtyp => 'BLANKET' --'STANDARD' for standard PO
,p_lineid => 7885858 -- null if you want to FINALLY CLOSE at header level
,p_shipid => NULL
,p_action => x_action
,p_reason => NULL
,p_calling_mode => x_calling_mode
,p_conc_flag => x_conc_flag
,p_return_code => x_return_code_h
,p_auto_close => x_auto_close
,p_action_date => SYSDATE
,p_origin_doc_id => NULL
);

IF x_returned = TRUE
THEN
DBMS_OUTPUT.put_line
( 'Purchase Order which just got Closed/Finally Closed is '
|| po_head.segment1
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line
('API Failed to Close/Finally Close the Purchase Order');
END IF;
END LOOP;
EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line

('Program failed with unexpected error');

END;

JDeveloper and OAF

How to find correct version of jDeveloper.

1) Login to EBS.
2) In Home page , lower left corner select About this page.

3) Select Technology component and in that we can see OA Framework version

4) For aboove version we can find the required jdeveloper patch in following metalink



416708.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x


Profile Options:

1) getting personalization link on page set this profile option-*Personalize Self-Service Defn* to yes

2) for getting about link on page set this profile option-*FND: Diagnostics* to yes.

3) Set profile "Sign-On:Notification" to "No" at site level

Oracle Patches

Tables: AD_APPLIED_PATCHES , AD_BUGS( bug number is the patch number)

Links:
Note: 181665.1 - Release 11i Adpatch Basics
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=181665.1

Note: 457566.1 - Oracle Applications Patching FAQ for Release 11i -- Can I determine ahead of time how a patch will affect my system?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=457566.1

Oracle Applications Maintenance Utilities -- 11i
http://download.oracle.com/docs/cd/B25516_18/current/acrobat/11iadutil.pdf

Oracle Applications Maintenance Utilities -- R12
http://download.oracle.com/docs/cd/B40089_09/current/acrobat/r12adutil.pdf


How can I track my customizations? What happens to my customizations during patching?

You should apply patches first on a test system. Then, review the changes in the test system and identify the best way to re-integrate customizations affected by the patch.

If you have registered your customized files in $APPL_TOP/admin/applcust.txt, AutoPatch reviews the files to determine if any of those files will be replaced during the application of the patch.

FND LOAD Scripts in Oracle

Forms Personalizations:
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt FND_FORM_CUSTOM_RULES function_name="OZF_FNDLVMLU"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt


Form Function:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt FUNCTION FUNCTION_NAME='((func name))'
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt


Lookups :
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ="XXDIS" LOOKUP_TYPE="((lkp name))"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt


Profile:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD @FND:patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt PROFILE PROFILE_NAME="((NAME_OF_profile))" APPLICATION_SHORT_NAME="CANON"


Concurrent Program:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt PROGRAM APPLICATION_SHORT_NAME="XXDIS" CONCURRENT_PROGRAM_NAME="((prg_name))"


Request Group:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt REQUEST_GROUP REQUEST_GROUP_NAME="System Administrator Reports" APPLICATION_SHORT_NAME="FND"

Flexi field:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'

Flexi field ....category/CONTEXT
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE="((FLEX_CONTEXT))"


Valuset :
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt VALUE_SET FLEX_VALUE_SET_NAME="CANON_E379_MERCH_DROPSHIP_WH_VS"
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt


Alert :
-------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt ALR_ALERTS APPLICATION_SHORT_NAME='WSH'
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt

POCIRM-222: --

Cause: POCIRM encountered an internal error. Parameters: REQIMPORT,,,ITEM,,N,Y,

Action: Note both this error number and the actions you are trying to perform. Contact your system a
POCIRM-222: ORA-06502: PL/SQL: numeric or value error: character string buffer too

Cause: A SQL error has occurred in POCIRM. The SQL error is &LSQL_ERR.

Action:To implement the solution, please execute the following steps
1. DBA should disable/ drop trigger ADS_PO_REQUISITION_HDR_TRG1. This
trigger was created by the ADS team for Demo purpose ONLY. No customer
instance should have this trigger.
This trigger is not a part of the seeded code and can be dropped.]

Document ID: 464573.1 for more detail

SQL Loader Case Studies

http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67792/ch04.htm

Spliting One row into Multi rows

select * from (
with t as (select 'A-INSTALL,6-FOLLOWUP' str from dual)
SELECT trim(REGEXP_SUBSTR (str,'[^,]+' ,1,level))
FROM t
connect by instr(str, ',', 1, level - 1) > 0
and connect_by_root str = str)

Concatenating Multiple Rows into single row

SELECT customer_product_id,
SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(incident_number, '/') ,
'/',' ,')),3) Concatenated_String
FROM (SELECT cia.incident_number,cia.customer_product_id
,ROW_NUMBER () OVER (PARTITION BY customer_product_id ORDER BY customer_product_id) row#
FROM cs_incidents_all cia
,csi_item_instances cii
WHERE 1 = 1
AND cia.customer_product_id = cii.instance_id
AND cii.serial_number = 'XXXXXX'
AND NOT EXISTS (
SELECT 1
FROM cs_incident_statuses
WHERE NAME IN ('Cancelled', 'Closed')
AND incident_subtype = 'INC'
AND incident_status_id = cia.incident_status_id))
START
WITH ROW#=1
CONNECT
BY PRIOR row# = row#-1 and prior customer_product_id = customer_product_id
GROUP
BY customer_product_id

Back to Back Orders (B2B)

In Drop-ship items are directly shipped to customer from the supplier and only logical receiving is performed in Oracle. In B2B orders items are physically received to Oracle from supplier and later they are shipped to customers.

Ex: When an order for Laptop is placed, you cannot send laptop and charger differently to the customer. If the company is not interested in maintaining the inventory of chargers, B2B is perfect solution as laptop charger order will go out when ever an order is created for laptop.And the charger is received to oracle and can be shipped with the Laptop.

Flow status code of the order line --FSC
Item reservation type ….IRT

1. Enter sales order …source code Internal
2. Book the order, at this time FSC – Supply Eligible
3. Perform progress order … and FSC –PO Req. Requested & IRT inventory
4. Req. Import –FSC –PO Req. Created & IRT external requisition
5. Auto Create PO –FSC – PO created & IRT PO order
6. Perform receiving transaction--- FSC Awaiting shipping& IRT Inventory
After this complete the order as normal sales order.

Important Notes:
Items used in Back to back order should be ATO enabled, Build in WIP flag checked and in general planning set the Buy flag.
In B2B order at some point we will physically receive goods before shipping them out, where as in Drop ship goods are directly shipped to Customer
Drop ship order may connect to more than one PO but B2B is connected to single PO.

SetupDocument Link:

http://functionalguy.blogspot.com/2009/02/back-to-back-order-cycle.html

Drop Shipment

It means you can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer

Drop Shipment - Process Steps
• Create a Sales Order with line where the line source is External
• Book and Schedule the Sales Order this will populate data in OE_DROP_SHIP_SOURCES
• The Purchase Release program this will populate data into PO_REQUISITIONS_INTERFACE_ALL
• Run Requisition Import Process PO_REQUISITION_HEADERS_ALL/ LINES/DISTRIBUTION
• Now the line status will be in Awaiting Receipt
• Login to the Receiving Organization (Purchasing) who has been setup as an Approver
• Create Purchase Order from the Requisition , populates data into PO tables
• Approve the PO
• Receive the full quantity
• Run Auto Invoice
• Verify Invoice in Sales Order

Things not to forget in a Drop Shipment

• Release 11i/12 does not support Drop Shipment across operating units.
• Blanket PO's will not used with Drop Shipment, the reason the PO must be created when OM notifies PO that a Drop Ship order has been created.
• You can't cancel Drop Shipments once Oracle Purchasing obtains the receipt.
• Standard Items can be used for Drop Shipment.

TABLES Involved
• PO_LINE_LOCATIONS_ALL
o This table stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs.
o This table stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs.
• PO_HEADERS_ALL/ PO_LINES_ALL/ PO_DISTRIBUTIONS_ALL
• PO_REQUISITION_HEADERS_ALL/ PO_REQUISITION_LINES_ALL/ PO_REQ_DISTRIBUTIONS_ALL
• PO_REQUISITIONS_INTERFACE_ALL
o This is the Requisition Import interface table.
• OE_ORDER_LINES_ALL/OE_ORDER_LINES_ALL/OE_DROP_SHIP_SOURCES
o This table stores relationships between order lines in OE_ORDER_LINES_ALL table and associated oracle purchasing requisitions in PO_REQUISITIONS_ALL and Oracle purchasing purchase orders in PO_LINES_ALL.
• RCV_SHIPMENT_LINES
o This table stores receiving shipment line information

Purchase Order Interface -- PO_HEADERS_INTERFACE

Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

Requisition Interface - PO_REQUISITIONS_INTERFACE_ALL

-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation
,line_attribute15 ---xtra infomation
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation
,rec_get_lines_info.ship_to_org_id ---xtra infomation
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id
, rec_get_lines_info.vendor_site_id
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);


Note:

Standard grouping rules provided by Oracle are by
Buyer
Category
Item
Location
Vendor or
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1

Concurrent Program:

apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'REQIMPORT' --Program,
argument1 => 'INV' --Interface Source code,
argument2 => '' --Batch ID,
argument3 => 'ALL'--Group By,
argument4 => ''--Last Req Number,
argument5 => ''--Multi Distributions,
argument6 => 'N' --Initiate Approval after ReqImport
);

E-Mail using PLSQL

CREATE OR REPLACE PROCEDURE plsql_mail_test IS
c UTL_SMTP.connection;
v_sent_date VARCHAR2 (30);
v_dns_name VARCHAR2 (100);
v_sender VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_recepient VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_subject VARCHAR2 (100) := 'TEST';
v_message VARCHAR2 (100) := NULL;

PROCEDURE send_header (
NAME IN VARCHAR2
,header IN VARCHAR2
) AS
BEGIN
UTL_SMTP.write_data (c, NAME
|| ': '
|| header
|| UTL_TCP.crlf);
END;


BEGIN

SELECT SYSDATE
INTO v_sent_date
FROM DUAL;

c := UTL_SMTP.open_connection ('xxx.xxx.xxx.xxx'); --server ip
UTL_SMTP.helo (c, 'XXXXXX'); --server name
UTL_SMTP.mail (c, v_sender);
UTL_SMTP.rcpt (c, v_recepient);
UTL_SMTP.rcpt (c, 'xxxxx@xxxx.com');
UTL_SMTP.open_data (c);
send_header ('From', v_sender);
send_header ('To', v_recepient);
send_header ('Subject', v_subject);
send_header ('Date', v_sent_date);
UTL_SMTP.write_data (c, UTL_TCP.crlf
|| v_message);
UTL_SMTP.close_data (c);
UTL_SMTP.quit (c);

EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
UTL_SMTP.quit (c);
raise_application_error (-20000
, 'Failed to send mail due to the following error: '
|| SQLERRM);

END plsql_mail_test;
/

Useful apps websites

www.erpschools.com

http://www.oracleappshub.com/

http://www.oracle.com/technology/documentation/applications.html

Creating Custom View(Synonym) in R12

In R12, Org Views of 11i are not used to filter data based on operating unit. Instead few synonyms are enabled with 'Security Policy' to filter data for a single operating unit.

'Fine-Grained Access Control' security policy replace CLIENT_INFO(Org Context) of accessing Multi-Org data.

'Security Profile' and calls to MO_GLOBAL package can be used to initialize and obtain access to Operating Unit specific data. All custom forms, reports and packages need to be initialized and make appropriate call to secure access for operating unit specific data.

Metalink reference:

Note:420787.1
Note:414013.1

Example:

CREATE SYNONYM APPS.DPMI_SO_TEST FOR
DPMI_MFG.DPMI_SO_HEADERS_INTERFACE_ALL

begin
dbms_rls.add_policy ('apps',
'DPMI_SO_TEST', -- synonym name
'ORG_SEC', -- use 'ORG_SEC' here
'apps',
'MO_GLOBAL.ORG_SECURITY', -- Standard MO VPD
policy
'SELECT, INSERT, UPDATE, DELETE',
TRUE,
TRUE,
FALSE,
DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
end;