LCM(Landed cost management) as Service(Black Box) Transaction flow step by step guide

BLACK-BOX SERVICE TYPE  LCM TRANSACTION FLOW STEPS

Step1: Responsibility: Purchase Order Super User.

           Navigation: Oracle Purchasing--> Purchase Order--> Purchase Orders.

            Create Purchase Order with LCM enabled Organization.

            Supplier: MATERIAL SUPPLIER

            Supplier Site: Chennai

            Ship to: ICD_LCM_Org (LCM enabled Org.)

            Line: (Item): RMLCMSER001

            (Make sure Inventory Asset flag = Y in this org )
            Quantity: 1 

            Price:2500(INR)

            Receipt Routing = “Standard Receipt”                                 

            Match Approval Level = “2-Way”                         

            Invoice Match Option =“Receipt”->it is mandatory for LCM actual management      

            Destination type =  “Inventory”    
            Save and approve the purchase order.  

Step 2:Responsibility: Purchase Order Super User.

             Navigation: Oracle Purchasing Super User-->Receiving-->Receipts  

             Create a Receipt for the Standard PO created above                        

             Choose Ship To Organization.                              

             Enter “Purchase Order” field as the PO number                               

             Click on “Find” button          

             Select the line and save the record in order to generate the Receipt Number.

Step 3:Responsibility: Purchase Order Super User.

           Navigation: Oracle Purchasing Super User-->View-->Request-->
           Submit a New Request--> “Landed Cost Integration Manager” concurrent program.     

           

Step 4: Responsibility: Landed Cost Management Responsibility.

            Navigation: Landed Cost Management Responsibility-->View-->
      Request-->Submit a New Request-->“Shipments Interface Import” concurrent program.  

Step 5: Responsibility : Landed Cost Management.

            Navigation:  Workbench --> Shipments

            Choose LCM enabled Inventory Organization 

            Click Go

            Click Line Groups

            Group Reference = 15160010 “Receipt Number”

            Click Go          

            LCM Shipment created for the receipt appears at the bottom of the page.

           

 Step 6: Choose “View Landed Cost” from the “Actions” and click on  “Go” button.    

          The “Allocations” page, with detailed information of the landed 

          costs of the shipment  will appear.

Step 7: Responsibility : Payable Super User.

            Navigation: Payable Super User-->Invoices-->Entry-->Invoices

            Create a payable invoice by PO match for creating the Material PO Invoice.

Step 8: Similarly the Payable Invoice is created for Freight Invoice.

            Responsibility : Payable Super User.

            Navigation: Payable Super User-->Invoices-->Entry-->Invoices

Step 9: Alternatively if the Freight charges are changed at the time of 

            creation of Payable Invoice.

              Then create the Invoice with the new Freight charge.

Step 10: Responsibility: Landed Cost Management Responsibility.

             Navigation: Landed Cost Management Responsibility-->View-->
             Request-->Submit a New Request-->“Match Interface Import” 
             concurrent program.
             This Request is for updating the Freight charges in the Landed Cost
             Charge with the new value.

Step 11: In Order to find the new Freight Charges in the Landed Cost

              Responsibility : Landed Cost Management.

              Navigation:  Workbench --> Shipments

              Choose LCM enabled Inventory Organization 

              Click Go

              Click Line Groups

              Group Reference = 15160010 à “Receipt Number”

              Click Go          

          

Step 12: Responsibility: Landed Cost Management Responsibility.

              Navigation: Landed Cost Management Responsibility-->View-->
              Request-->Submit a New Request-->“Submit Pending Shipments”
              concurrent program.

        

   

How to print Attached text file in PO to a Purchase Order Print out ?

function CF_ATTACHFormula return Char is
V_ATTACH Varchar2(32000);
V_LONG_TXT LONG(32000);
begin
  SELECT DLT.LONG_TEXT INTO V_LONG_TXT
  /*AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DLT.LONG_TEXT */
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_LONG_TEXT DLT    ,
  PO_HEADERS_ALL    PHA
WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
AND DCT.CATEGORY_ID       = D.CATEGORY_ID
AND D.DATATYPE_ID         = DAT.DATATYPE_ID
AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID          = D.MEDIA_ID
AND DAT.NAME              = 'LONG_TEXT'
AND ENTITY_NAME = 'PO_HEADERS'
AND AD.PK1_VALUE  = PHA.PO_HEADER_ID
AND PHA.SEGMENT1 = :PO_ORDER;

DECLARE
   f_line       VARCHAR2 (32000);
   f            UTL_FILE.file_type;
   f_dir        VARCHAR2 (250);
   fname        VARCHAR2 (50);
   Comma1       VARCHAR (10);
   Comma2       VARCHAR (10);
   Comma3       VARCHAR (10);
   Comma4       VARCHAR (10);
   Comma5       VARCHAR (10);
 
BEGIN
   f_dir := '/usr/tmp';
   fname := 'TEST.txt';
   f := UTL_FILE.fopen (f_dir, fname, 'r');

 
   LOOP
      BEGIN
        
          UTL_FILE.get_line (f, f_line);
        EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

    
   V_ATTACH := V_ATTACH || CHR(10) ||f_line;
   
   SRW.MESSAGE(10002, V_LONG_TXT);

   ---   DBMS_OUTPUT.put_line(  f_line );

     
   END LOOP;

   UTL_FILE.fclose (f);
   COMMIT;
  
   Return V_ATTACH;
  
END;
end;

How to find OPM Batch Close Variance

Batch Close Variance Checking Code

Select aa.ENTERED_AMOUNT,aa.* from gmf_xla_extract_lines aa

where journal_line_type ='CLS'

and event_id in(select event_id from gmf_xla_extract_headers

where transaction_date >='01-SEP-2014'

and transaction_date <='30-SEP-2015');

How to find OPM Batch Close Variance for individual Batch

 Individual Batches with corresponding Close Variance Amount:

select batch_det.batch_no batch_no, batch_det.organization_id , gmf_cls.amount amount 

from

(Select gxel.accounted_amount amount,gxel.event_id,gxeh.event_id,gxel.header_id,gxeh.header_id,gxeh.source_document_id source_document_id,gxeh.source_line_id source_line_id

from gmf_xla_extract_headers gxeh,

gmf_xla_extract_lines gxel

where gxeh.TRANSACTION_DATE >=TO_DATE(:START_DT)

and gxeh.TRANSACTION_DATE <=TO_DATE(:END_DT)

and gxel.journal_line_type ='CLS'

and gxel.event_id = gxeh.event_id)gmf_cls,

(select gbh.batch_id batch_id,gbh.batch_no batch_no, gbh.ORGANIZATION_ID organization_id

from gme_batch_header gbh

where gbh.PLAN_START_DATE >=TO_DATE(:START_DT)) batch_det

where gmf_cls.source_document_id=batch_det.batch_id

LCM(Landed cost management) as Pre-Receiving Transaction flow step by step guide

PRE-RECEIVING LCM TRANSACTION FLOW STEPS

Step1: Responsibility: Purchase Order Super User.

           Navigation: Oracle Purchasing--> Purchase Order--> Purchase Orders.

            Create Purchase Order with LCM enabled Organization.

            Supplier: MATERIAL SUPPLIER

            Supplier Site: Chennai

            Ship to: ICD_LCM_Org (LCM enabled Org.)

            Line: (Item): RMLCM00002

            (Make sure Inventory Asset flag = Y in this org )

            Quantity: 1 

            Price: 2000 (INR)

            Receipt Routing = “Standard Receipt”                                 

            Match Approval Level = “2-Way”                         

            Invoice Match Option =“Receipt”->it is mandatory for LCM actual management      

            Destination type =  “Inventory”    

            Save and Approve the Purchase Order.  

    

 Step 2: Responsibility : Landed Cost Management.

             Navigation:  Workbench --> Shipments

             Header:Choose the LCM Enabled Inventory Organization(ICD_LCM_Org)

             Create a new Shipment

             Choose the Operating Unit 

             Choose the Shipment Type

             Select the Receiving Location and Shipment Date

             Line Group: Select the Group .

             Enter a Group Reference : Material Shipment

             Choose the Source Type: Purchase Order

             Third Party: Choose the Supplier of the Purchase Order

             Third Party Site: Chennai (Supplier Site)

             Click on Save and then Apply.

             Displays

             Confirmation         

             The Shipment 1 has been saved successfully.

             Click on Lines Tab

             Choose the Approved Purchase Order  and Click on Go.

             Select the Line , Click on Save and Apply.

Step 3: Choose the Validate option from the Actions

             Choose the Manage Charges Option  From Actions and then click on Go.

             Charges Line : Select in the Line 1 the Type option for Freight

             Select the Third Party for Freight Charges (FREIGHT SUPPLIER)

             Select the Third Party Site (i.e. Salt Lake)

             Enter the Freight Amount

             Associations: In Type select any Type i.e Shipment Line Number.

             Similarly select for Insurance.

 

Step 4: Select the Validate option from Actions and then click on Go.

 

Step 5: Select the Calculation option from Actions and then click on Go.

Step 6: Click on View Landed Cost option from Actions and click on Go, to find out the details of the 

             Landed Cost.

 

     Click on the Record to get the full detailed graphical  landed cost.

 

Step 7: Click ‘Return to Shipment’ Link

             Choose  Actions = Submit

             Click Go

             Select Submit option from Actions and then click om Go.

             The status of the line will be changed to Completed status.

 

Step 8: Receive LCM Shipment

            Navigation: Oracle Purchasing Super User-->Receiving-->Receipts           

            Choose Organization

            Select Source Type = LCM

            Click LOV of Shipment

 

 

              After Receiving the purchase order deliver the item.

Step 9: Item Cost Details

             Navigation : Cost Management SLA--> Item Cost--> Item Cost History

             Select the item and Click on find.

             In the Item cost both the purchase order cost with the Freight Cost and Insurance Cost

             added in the Item cost.

 

Step 10:  Payable Invoice creation.

                 Navigation: Payable Super User-->Invoices-->Entry-->Invoices

                 Create a payable invoice by PO match for creating the Material PO Invoice.

   

 

Step 11: Another Payable Invoice is to be created for Freight Cost 

              Create a Invoice with the Freight Supplier and then match the Invoice with the Material Supplier.

 

     Match the Freight amount with Material Supplier with the Purchase Order and Receipt number.

     Click on Find.

 

 Step 12:    Select the  the Cost Factor name and enter the exact Freight amount.

                                 

 

  Step 13: If at the time of Invoicing the Freight Cost Increases, then after selecting Cost factor enter the 

                 new Freight charge amount. Validate the Payable Invoice.

            

 

 Step 14: New Freight Cost is to be updated in the Landed Cost also.

                Navigation: Landed Cost Management Responsibility--> View--> Submit New Request-->

                "Matches Interface Import ".

                 Workbench--> Shipments

                 Search the transaction , Select View Landed Cost from Actions and Click Go

 

   Now if the item cost is checked , it also gets updated.

 

  Similarly for the Insurance Supplier Invoice can be created and updated with the new cost.