Calling Request Set from PLSQL on Specific Time

CREATE OR REPLACE PROCEDURE XXAP_SUBMIT_REQUEST_SET (
P_errbuf    OUT VARCHAR2,
P_retcode   OUT NUMBER)
AS
V_REQUEST_SET_EXIST   BOOLEAN := FALSE;
req_id                INTEGER := 0;
l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
srs_failed            EXCEPTION;
submitprog_failed     EXCEPTION;
submitset_failed      EXCEPTION;
l_start_date          VARCHAR2(250);
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Calling set_request_set…');
V_REQUEST_SET_EXIST :=
FND_SUBMIT.set_request_set (application   => 'EC',
request_set   => 'JM856ASNOUTBOUND');

IF (NOT V_REQUEST_SET_EXIST)
THEN
RAISE srs_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling submit program first stage');
l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW2',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW1',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'XXJM856OUTBOUND_WLMRT',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

/*l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('XXAP',
'XXAP_FOURTH_PROGRAM',
'STAGE40');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;*/

fnd_file.put_line (fnd_file.LOG, 'Calling submit_set…');

--–l_start_date is to schedule the request
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')
  into l_start_date
FROM dual
WHERE 1                            =1
AND TO_CHAR( SYSdate,'HH24:MI:SS') > '13:00:00'
AND TO_CHAR( SYSdate,'HH24:MI:SS') < '14:30:00'
AND  TO_CHAR(SYSDATE,'DY')         IN('MON','TUE','WED','THU','FRI');

req_id :=
FND_SUBMIT.submit_set (start_time    => l_start_date,
sub_request   => FALSE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;
EXCEPTION
WHEN srs_failed
THEN
p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitprog_failed
THEN
p_errbuf := 'Call to submit_program failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitset_failed
THEN
p_errbuf := 'Call to submit_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN OTHERS
THEN
p_errbuf := 'Request set submission failed – unknown error: ' || SQLERRM;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
END;

Account Payables (A.P) Module:-

           Account payables will be used to do the payment transactions. A.P Module is integrated with both P.O and G.L Modules. In Account Payables we will create the invoices and we will approve once invoice is approved successfully we will make the payment. Once payment is over we will move the transactions from A.P to G.l.

1.  Without supplier we cannot create Invoice.

2.  Without invoice we cannot make Payment.                                      

From the company point of view a person or Organization who is going to receive amount we will call as Supplier.

Types of Invoices:-

1.     Standard

2.     Credit Memo

3.     Debit Memo

4.     With Holding Tax                                                                                   

5.     Po Default

6.     Mixed

7.     Pre Payment

8.     Expense Report

9.     Recurring Invoices

10.  Quick Match                                                                                

Standard Invoice:-    We will create the Standard Invoice to particular Supplier and Supplier site we will enter the invoice amount, invoice date and soon……..

Credit Memo & Debit Memo Invoices:- Both Invoices has got negative (-ve) amount and adjusted against Standard Invoice. Credit Memo will be created whenever Supplier is giving discount. Debit Memo will be created if buyer is going to deduct the amount.

With Holding Tax Invoice:-      If supplier is not registered supplier then buyer will make the Income Tax to the government on behalf of supplier.

Po Default Invoice:-    Here we will create the Invoice as per Purchase Order amount. We will give the Po number system will retrieve PO amount and Invoice will be created as per PO details.

Prepayment Invoice:-    When ever we want make payment to supplier in advance that tome we will create this Prepayment Invoice and we make the Payment.

Expense Reports Invoice:-     It will be created for employee expenses as per the employee grade, position this Invoices will be calculated.

Recurring Invoice:-      For some of the Invoices we will not be having supplier invoice that time we will create Recurring Invoices.

Ex:-  For rent account we will be creating Invoice which has got fixed amount and fixed rate (duration).

 Quick Match Invoice:- While creating Purchase Order we will be giving the match approval option as per that match approval we will create the Invoice and the Invoice type is Quick Match Invoice.

Mixed Invoice:- Mixed Invoices will be created for miscellaneous expenses. Once we create the invoice you have to do following 3 activities.

1.     Validate Invoice

2.     Approve the Invoice

3.     Create Accounting entries for Invoice 

 INVOICES

Here we will select the Invoice type and we will give the Supplier number, name, site invoice date, invoice number, invoice currencies, and amount. Select Distributions button to distribute the Invoice amount into different accounts.

1.     Invoice total should be equal to the distributions total then we will call it as Invoice validated successfully.

2.     Select Actions…1 button chooses approve check box press OK then system will approve the Invoice.

3.     Select Actions…1 button choose create accounting check box press OK button it will create the accounting entries we can see all this accounting transactions from tools view accounting option.

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV4516'  --INVOICE_ID=63379 ,--VENDOR_ID(LINK B/WAP INVOICE AND PO_VENDORS
)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=63379

Invoice Holds:-    If invoice is not approved then that invoice will be keeping under hold status. By selecting holds button in invoice form we can see the holds details.

For view Invoice holds details:

           Select * from ap_holds_all

For view release the Invoice holds names:

           Select * from ap_holds_release_name_v;

PAYMENTS:

Payments:-     Once the Invoice is approved then we can go for payments. The Payments are or 3 types. They were

1.     Manual

2.     Quick

3.     Refund

Manual:-    Here we will issue the checks manually to the supplier and we will capture that information in the payment scheme by using manual payment option.

Quick:-     Through the Quick Payment type we can generate checks through the system and we can have the transactions directly in the system.

Refund:-   When ever company is going to give advance back to the customer that time we will select payment type as Refund.

Navigation steps for Payments:-

                 payments  ==> payments

For view list of payments:

           Select * from ap_invoice_payments_all;

           Select * from ap_payment_schedules_all;

For check’s information:

           Select * from ap_checks_all;

For check format:

           Select * from ap_check_formats;

           Select * from ap_checkrun_conc_processes_all;

Distribution Set:-     It is one of the option is available in Invoices Screen. While creating the Invoice we will attach distribution set. System will automatically create the transactions in distributions forms as per the distribution set.

 Navigation:

 set-up =>invoice=> distribution set

To view Distribution sets at header level:

           Select * from ap_distribution_sets_all;

To view Distribution sets at lines level:

           Select * from ap_distribution_set_lines_all;

Transferring Transactions from AP to GL:-

           We will execute the concurrent program from SRS Window. This program will transfer all the payment transactions into the G.L Module. It will take following parameters.

Program Name:-   Payables Transfer to General Ledger

Parameters:-

           Set of Books Name

           Transfer Reporting Book(s)

           From Date

           To Date

           Journal Category

Validate Accounts

Transfer To GL Interface

Submit Journal Import :  yes  (It should be always YES)

To view from AP to GL:

           Select * from gl_interface;

To view journal import details:

           Select * from gl_je_headers  à       for Headers

           Select * from gl_je_lines      à       for Lines

           Select * from gl_je_batches  à       for Batches

To view posting:

          

           Select * from gl_balances;

          

           After submitting the request select viewà output button. It will shows number of transactions has been transferred to G.L. then select G.L Module (General Ledger, Vision Operations (USA)).

SELECT * FROM GL_JE_HEADERS

SELECT * FROM GL_JE_LINES

SELECT * FROM GL_JE_BATCHES

SELECT * FROM GL_BALANCES

P2P(Procure-to-Pay) Cycle Tables with Joins

P2P(Procure-to-Pay) Cycle Tables with Joins

Pre Requisites for Purchase Order:

1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

-------------------------------------------------

1.Item Creation:

1)Attach the Responsibility called "Inventory Vision Operations (USA)"

2)Open the Items form

  Items=>Master Item

3)Select the Organization name - Vision Operations

4)Enter the Item Name , Item Description 

  goto Inventory tab check the checkbox called Inventory 

  goto purchasing tab check the check box called Purchasing

5)Save

6)Goto  Tools Menu => Organization Assignment option to assign for the multiple

  organizations.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'  --INVENTORY_ITEM_ID=20817

2.Supplier Creation:

1)Attach the Responsibility called "Purchasing Vision Operations (USA)"

2)Goto the Supplier Form

  Supply Base=>Suppliers

3)Enter the Supplier Name . Save supplier number will be created automatically.

4)Select the Sites button enter the supplier site address and other details

5)Goto the Contacts tab enter the Contact details 

    Name

    Phno

    Postion and so on.....

SELECT * FROM PO_VENDORS WHERE SEGMENT1='20067' --VENDOR_ID=7930

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=7930 --VENDOR_SITE_ID IN (4638,4639)

SELECT * FROM PO_VENDOR_CONTACTS WHERE VENDOR_SITE_ID IN (4638,4639)

3.Buyer Creation:

Buyer Creation:

==================

1)attach the Responsibility called "US HRMS Manager"

2)Create Employee  

  People => Enter and Maintain=>Select New button

3)Enter Emp name 

  select action option select "create Employement" select the optiona s "Buyer"

4)enter Data of Birth 

  save => Ok  = > Empoyee number will be generated.

5)Goto  System Administrator open the User form create or query user

  select the Person field attach the emp name (Which we have created)

6)Save.

7)Goto Purchasing Responsibility 

  open the Buyers form 

  enter the employee name and save the transactions.

 Setup => Personnal =>Buyers

SELECT * FROM PER_ALL_PEOPLE_F --WHERE --PERSON_ID='25'--EMPLOYEE_NUMBER='1289' --PERSON_ID=13496

Purchase Order Flow:

1.Requisition
2.RFQ(Request for Quatation)

1.REQUISITION:

Requisition: is one of the purchasing document will be prepared by the employee when
   ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
                    2)Purchase

Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.

Requisitions=>Requisitions

We will enter the Requisition at three level 1)Header
                        2)Line
                        3)Distributions.

Open the Requisition form enter the Reqno and select the type at Header level
 Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu  =>Control option to Cancel the requisition.


SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5716' --REQUISITION_HEADER_ID=56885

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=56885  --REQUISITION_LINE_ID=60797

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=60797

2.RFQ(REQUEST FOR QUOTATION)



Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents

BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
        PriceBraeaks(Discounts).

catalog RFQ: This will be create for te materials which we will purchase from the
        suppliers regularley , and large number of quantity. Here we can specify the
        Price Breaks.

Standard RFQ:  This will be prepared for the Items which we will purchase only once
        not very often,Here we can include the Discounts information at different
        auantity levels.
RFQ Information will be entered at 3 Level
  1)Headers
  2)Lines
  3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)

Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.

Payment Terms: When Organization is going to make the payment and Interest rates
Fright  Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
             the responsiboility of those materials.
Carrier     : In which Transportation Company Organization Required Materials
              Transportation company Name.

Open the RFQ Form

RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.


SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='347' AND TYPE_LOOKUP_CODE='RFQ' --PO_HEADER_ID=32876

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=32876 -- PO_LINE_ID=38063

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38063 --LINE_LOCATION_ID=72425

3.QUOTATION:



Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.

Whatever the quotations we have received from the supplier we will enter in the system
through form.

We have three types of Quotations 1)Bid 2)Catalog 3)Standard

For Bid RFQ      we will  receive Bid      quotation from the Supplier
For Catalog RFQ  we will  receive Catalog  quotation from the Supplier
For Standard RFQ we will  receive Standard quotation from the Supplier.

After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.

Quotation Report

Item Name   (Table Value set MTL_SYSTEM_ITEMS_B   Segment1)

QuoteNo Type  Cdate Supplier Site ContactPerson Buyer   Created(UserName)

4.PURCHASE ORDER:

PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.

We have four types of Purchase Order       1)STANDARD
     2)PLANNED
     3)BLANKET
     4)CONTRACT

Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form  enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document  => To Create Another PO based on this PO
Control        => To Close the Purchase Order or to cancel the Purchase Order.

Types of Purchase Order: -

	Standard P.O	Planned P.O	Blanket P.O	ContractP.O
Terms and Conditions Goods or Service Known Pricing Known Quantity known Account Distributions Known Delivery Schedule Known Can be Encumbered Can Encumber releases	Yes Yes Yes Yes Yes Yes Yes N/A	Yes Yes Yes Yes Yes May be Yes Yes	Yes Yes May be No No No No Yes	Yes No No No No No No N/A

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='4514' AND TYPE_LOOKUP_CODE='STANDARD' --PO_HEADER_ID =32878
 --TYPE_LOOKUP_CODE

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =32879 --PO_LINE_ID=38065

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38066 --LINE_LOCATION_ID=72427

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=72428

SELECT * FROM PO_LOOKUP_CODES --LOOKUP_CODE

AUTO CREATE:


It is one of the Purchasing feature to create the RFQ and  PO documents automatically
by using requisition lines.

1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
  select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
  AddTo  to add lines to exisiting to RFQ
6)Select DocumentType = RFQ
7)select Automatic button which will create RFQ document automatically .

5.RECEIPTS:

           Receipts are one of the documents it will be used to find out how much quantity Supplier has supplied. We will find out Purchase Order status if it is successfully approved then we will create the Receipt. We will give the PO Number select Findbutton check the PO lines right mark and save. It will create the Receipt number select Header button it will shows the receipt number and date.           

                                                                                                                                                               SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=7472 --SHIPMENT_HEADER_ID=61421

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=61421 -- SHIPMENT_LINE_ID=68368

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_HEADER_ID=61421

Once Receipt will created go Inventory module and check whether the requested items are received or not by using these tables.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'

SELECT * FROM MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID=20817 AND ORGANIZATION_ID=204

SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=32881 AND TYPE_LOOKUP_CODE='STANDARD'

Match Approval Level :- While creating the Purchase Order we will mention the Match Approval Level at Shipments we will have 3 types they are

2-way:- Purchase Order and Invoice Quantities must match within tolerance before the corresponding invoice can be paid.

3-way:- Purchase Order, Receipts and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

4-way:- Purchase Order, Receipts, Inspection and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

R12 SLA (Sub ledger Accounting)
Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.
1) All accounting performed before transfer to the GL. Accounting data generated and stored in "Accounting Events" tables prior to transfer to GL


2) Run "Create Accounting" to populate accounting events (SLA) tables. User can "View Accounting" only after "Create Accounting" is run. Create Accounting process
– Applies accounting rules
– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA "distribution links" table


3) Below are the key tables for SLA in R12


XLA_AE_HEADERS xah
XLA_AE_LINES xal
XLA_TRANSACTION_ENTITIES xte
XLA_DISTRIBUTION_LINKS xdl
GL_IMPORT_REFERENCES gir


Below are the possible joins between these XLA Tables


xah.ae_header_id = xal.ae_header_id
xah.application_id = xal.application_id
xal.application_id = xte.application_id
xte.application_id = xdl.application_id
xah.entity_id = xte.entity_id
xah.ae_header_id = xdl.ae_header_id
xah.event_id = xdl.event_id
xal.gl_sl_link_id = gir.gl_sl_link_id
xal.gl_sl_link_table = gir.gl_sl_link_table
xah.application_id = (Different value based on Module)


xte.entity_code =
'TRANSACTIONS' or
'RECEIPTS' or
'ADJUSTMENTS' or
'PURCHASE_ORDER' or
'AP_INVOICES' or
'AP_PAYMENTS' or
'MTL_ACCOUNTING_EVENTS' or
'WIP_ACCOUNTING_EVENTS'


xte.source_id_int_1 =
'INVOICE_ID' or
'CHECK_ID' or
'TRX_NUMBER'


XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types


xdl.source_distribution_type = 'AP_PMT_DIST'
and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id
---------------
xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id
---------------
xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id
and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
---------------
xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id
---------------
xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id
---------------
xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id
---------------
xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id.

          Hope this will help you.

OM Related Queries

OM Related Queries

OM Related Queries

SALES ORDER FLOW

Navigation: Orders. Returns -> Sales Orders

1. Enter Customer and Order Type. The other fields may default values. If “Ship To Location”, “Bill To Location” and “Price List” are not defaulted, pick those values from “LOV”

2. All the fields should default in Others tab. If Payment Terms or Warehouse is not defaulted, choose values from LOV. Warehouse parameter stores the warehouse from which the order will be shipped

3. Enter “Ordered Item”, “Qty”, “UOM”.

Note: The item should already be setup in Inventory. Please refer to Item Setup for the item setup information. The item price should exist in the price list specified at the order header level. Please refer to Price List Setup section for price list setup.

Base Tables: Header Level : oe_order_headers_all

Colums: flow_status_code= “ENTERED” and booked_flag=”N”

Line Level : oe_order_lines_all

Columns : flow_status_code= “ENTERED” and open_flag=”Y”

Book Sales Order

Book the order by clicking on “Book Order” button

Base Tables: Header Level : oe_order_headers_all

Colums: flow_status_code= “BOOKED” and booked_flag=”Y”

Line Level : oe_order_lines_all

Columns : flow_status_code= “WAITING SHIPING” and booked_flag=”Y”

Shipping: wsh_delivary_details

Columns released_status=”R” ‘Ready To Release’

Wsh_delivary_assignments

Reservations: This step is required for doing reservations schedule order program run in backend and two tables are updated

Tables: mtl_demand and mtl_reservations

Pick Releasing a Sales Order:

Navigation: Shipping -> Release Sales Orders -> Release Sales Orders

1. Enter the Order Number that needs to be pick released

2. Specify the warehouse. This should be the same warehouse as specified in the order line.

3. Click on Concurrent button. This should launch “Pick Release” concurrent program

4. Click on “View” and then “Request” menu option. Click on “Find”. It should bring all the concurrent requests submitted by the user. There should be a request called “Pick Slip Report”. Click on “View Output” button to view the orders that are pick released.

Tables: wsh_delivary_details

Wsh_picking_batch

Wsh_new_delivary

Wsh_delivary_assignments

oe_order_lines_all flow_status_code= “PICKED”

mtl_onhand_quantities

mtl_transaction_accounts

Shipping a Sales Order:

Navigation: Shipping -> Transactions

1. Enter the Order Number in “From Order Number” field and click “Find” button

2. Click on Details button to enter the ship quantity

3. Enter “Shipped Quantity” and click Done

4. Click on the “Delivery” tab and select “Ship Confirm” for actions and click “GO” to confirm the shipment

Tables: wsh_delivary_details

Wsh_picking_batch

Wsh_new_delivary

Wsh_delivary_assignments

oe_order_lines_all flow_status_code= “SHIPPED”

mtl_onhand_quantities

mtl_material_transactions

Invoicing a Sales Order:

Navigation: Inventory -> Workflow Background Engine

1. Run this concurrent program to interface order to accounts receivables. Select “Yes” for “Process Deferred” and select “Yes” for “Process Timeout” parameters. Click on OK

Tables: wsh_delivary_details

Ra_interface_lines_all

Ra_customer_trx_all

Ra_customer_trx_lines_all

Ra_cust_trx_types_all

Viewing and Modifying an Invoice:

Navigation: Orders. Returns -> Sales Orders

1. Query the order that was invoiced (Press F11, Enter the Order Number and Press Ctrl F11). Right Click on Customer Field and choose “Additional Order Information” -> Invoices/Credit Memos

2. Note down the invoice number

Navigation: Receivables -> Transactions -> Transactions

3. Query the invoice using the invoice number. Order and Invoice can be verified for proper invoicing

Creating Receipt for the Invoice

Navigation: Receivables -> Receipts -> Receipts

1. Enter “Payment Method”, “Receipt Number”, “Receipt Amount” and “Trans Number”. Receipt Amount can be equal to the invoice amount and Trans Number is the invoice number for which the receipt was created.

TABLES: ar_cash_receipts_all

Ar_payment_schedules_all

Ar_cash_receipts_history_all

Ar_receivable_applications_all

Receivables Transfer to GL

Navigation: Receivables -> Interfaces -> General Ledger

1. Run General Ledger Transfer Program to Transfer AR data to GL

Creation of Pricelist (Copying)

Order Management Super User

(N) Pricing-> Price Lists -> Copy Price List

Base Tables Affected: qp_list_headers_b,

qp_list_lines

Switch Responsibility to Inventory Super User

Inventory Super User

Items -> Master Items

For Item to be used in transactions check the following tabs and mandatory fields.

Main Tab – Primary UOM, Pricing, Deviation Factor +/-

Inventory Tab – Inventory Item, Stackable, Translatable, Reservable need to be checked.

Purchasing Tab – Purchased, Purchasable, Allow Description Updatable need to be checked.

Order Management Tab – Customer Ordered, Customer Orders Enabled, Shippable, OE Translatable need to be checked

Invoicing Tab – Invoicable Item, Invoice Enabled.

Base Tables Affected: mtl_system_items_b

After that we have to assign the item in the Inventory Organizations.

(M) Tools -> Organization Assignments

Check the Checkbox Checked for all the organizations to which u want to assign the item to

Base Tables Affected: mtl_system_items_b (One Line created for each Organization)

We have to create the On-Hand Quantity for the Item Created for that you have to create a Miscellaneous Transaction.

(N) Transactions -> Miscellaneous Transaction

(N) Type -> Miscellaneous Receipt

(B) Transaction Lines

Then Enter the Item and Quantity and Save

Base Tables Affected: mtl_onhand_quantities

Attach the item created to a Price List.

Switch Responsibility to Order Management Super User.

(N) Pricing -> Price Lists -> Price List Setup

Base Tables Affected: qp_list_headers_b, qp_list_lines

Creating a Profile Class To attach to a customer.

(N) Customers -> Profile Class

Base Tables Affected: hz_cust_profile_classes

To Create Payment Terms

(N) Receivables -> setup -> Transactions -> Payment Terms

Base Tables Affected: ra_terms

To Disable the Automatic Numbering for the Locations while creating the customer.

(N) Receivables -> Setup -> System -> System Options

Uncheck the check box Automatic Site Numbering.

Base Tables Affected: ar_system_parametes_all

Customer Creation

(N) Customers -> Standard

Base Tables Affected: hz_parties, hz_party_sites, hz_locations, hz_cust_accounts, hz_cust_account_sites_all, hz_cust_site_uses_all, ra_customers

To update a customer profile which is already in use?

Do Not Update existing Profile

Update All Profiles

Updated All Uncustomized Profiles

(N) Customers -> Customer Profiles

Create Order Types, Line Types (Return and Standard)

(N) Setup -> Transactions Types -> Define

Base Tables Affected: oe_transaction_types_all,oe_workflow_assignments

Create a Document Sequence

·         Create a Sequence.

·         Create a Category

·         Assign the Sequence to the Category

Setup -> Documents -> Define

Base Tables Affected: fnd_document_sequences

Document Category Creation

(N) Setup -> Documents -> Categories

Base Tables Affected: fnd_doc_sequence_categories

Document Sequence Assignments

(N) Setup -> Documents -> Assign

Base Tables Affected: fnd_doc_sequence_assignments

Defaulting Rules

Create a Defaulting Rule Template

(N) Setup -> Rules -> Defaulting

(B) Defaulting Condition Templates

(B) Defaulting Rules

For Price List

Then Go to

(M) Tools -> Generate Default Handler Package.

Base Tables Affected: oe_def_attr_def_rules, oe_def_attr_condns, ak_object_attributes

Capturing End User Details (Install Base)

First Check the item is install base trackable or not?

(N) Order, Organizer -> Sales Order

(T) Lines

(B) Actions -> Installation Details

Base Table Affected: csi_t_party_details

Sales Credits - Practice.

(N) Orders. Returns -> Sales Orders

(B) Actions -> Sales Credits

Base Table Affected: oe_sales_credits

Creating a Ship Sets, Arrival Sets and Fulfillment Sets

Sales Order Screen

(T) Shipping and go to appropriates set and create new one

Attaching Documents to the Order

Go to Attachments

Base Tables Affected: fnd_attached_documents,fnd_documents_tl,fnd_documents_short_text

Blanket Sales Agreement

(N) Blanket Sales Agreements -> Blanket Sales Agreements

Base Tables Affected: oe_blanket_headers_all, oe_blanket_lines_all

(B) Submit Draft

Internal Orders

Prerequisites the internal Ordered Check Boxes need to be checked

Internal Orders

First we have to create the Purchase Requisition for the internal Order.

(N) Purchasing -> Requisitions -> Requisitions

You have to be setup as Employee to access purchasing

Change Responsibility to HRMS Manager

(N) HRMS Manager -> People -> Enter and Maintain

Base Tables affected: per_all_people_f

Now assign the employee to the user

Change Responsibility System Administrator

(N) Security -> User

Base Table affected: fnd_users

Change Responsibility to OM Super User.

(N) Purchasing -> Requisitions -> Requisitions

Base Tables Affected: po_requisition_headers_all, po_requisition_lines_all

Now the PO Requisition needs to be approved, so the user should have appropriate privileges

To Check the Status of the Purchase Requisition you have to check the Requisition Summaries screen

(N) Purchasing -> Requisitions -> Requisition Summary

We can check the status in a view po_requisition_headers_inq_v

After the Purchase Requisition has been approved then you have to run the Create Internal Orders Program to import the internal Purchase Requisitions to Internal Orders.

(N) Reports, Requests -> Run Requests

Check the Log to ensure that the Internal Requisition has been processed for the Order

The Above Process will take the PO Requisition up to the Order Interface Tables now we have to run the Order Import Program to Import the Internal Orders.

(N) Reports, Requests -> Run Requests

Now check for the Order Created for that PR

(N) Orders. Returns -> Order Organizer

Automatically the order created will be of type Mixed. To Change the Order Type of the Internal Order we have to set up that at the Organization Level.

(N) Inventory -> Setup -> Organization -> Purchasing Parameters

Now Pick Release

(N) Shipping -> Release Sales Orders -> Release Sales Orders

Ship Confirm

(N) Shipping -> Transactions

To Check the Internal Order Details.

Go to the Line and

(B) Actions -> Additional Line Information

(T) Internal Requisition.

Drop Shipment

Create Order, Line Level -> Shipping tab, Source type should be External

Now Create Purchase for the Order.

(N) Purchasing -> Reports -> Run

Step 1 – Creation of Purchase Requisition

Step 2 – Create PO from PR.

(N) Purchasing -> Requisitions -> Requisition Summary

(T) Related Documents

Search based on Sales Order Number

(N) Purchasing -> AutoCreate (To Crete a PR from PO)

Base Tables Affected: po_headers_all, po_lines_all

Now if we check the Order Line Status, it will be Awaiting Receipt.

Step 3 – Receiving

Change the Organization to – M1

Now we have to create the receipt for the PO

(N) Purchasing -> Receiving -> Receipts.

Save the work and it will trigger the following programs.

Apart from this it will also trigger the Payables open Import Program also.

Now if we check the status of the Line it will be shipped

To Check the Details of Drop Shipment then you have go to Additional Line Information.

(B) Actions -> Additional Line Information

After this step line will be awaiting fulfillment, so run the workflow background process and autoinvoice program so that invoice will be generated.

Scheduling

Scheduling Before Booking,

Initially Scheduled ship Date and Schedule Arrival date will be empty

(M) Tools -> Scheduling -> Schedule

With Scheduling the above mentioned fields will be populated automatically

We can do auto scheduling also.

(M) Tools -> Auto Scheduling (Checking the Check Box)

Profile Options:-

·         OM: Scheduling Role

Values – CSR Only – User Cannot Access The Scheduling

CSR and Scheduler – User Can Access All the Tabs

Scheduler Only – Only Scheduler Tab will be enabled

·         OM: Apply Automatic Attachments – Determine whether the rule based attachments are enabled without User Intervention.

·         OM: Autoschedule – Determines the default Settings of the autoscheduling.

·         OM: Invoice Source – This Value is transferred if the transaction type is null

·         OM: Invoice Transaction Type – This Value is transferred if the transaction type is null

·         OM: Item Flexfield – Determine the structure of the item Flexfield used in OM.

·         OM: Source Code – Value is defaulted to ORDER ENTRY and identifies the source code which passes to OM to inventory while scheduling.

·         OM: Sales Orders form preference – Confirms the default sales order window whether it is quick sales order window or the standard one.

·         Sequential Numbering – Required Profile option to determine the sequential number triggered in E-Business Suite.

You are not allowed to change the order once if the order is booked. This has been protected by the processing constraints.

If you want to update even if the order is booked, then this processing constraints need to be changed.

(N) Setup -> Rules -> Security -> Processing Constraints

Base Tables Affected: oe_pc_assignments, oe_pc_exclusions

Splitting Lines

Base Tables Affected: oe_order_lines_all

Line will be split into two lines and the split_from_line_id will be populated for the second line.

Managing Holds

(N) Setup -> Orders -> Holds

Base Tables Affected: oe_hold_definitions, oe_hold_authorizations

Applying Holds

(B) Actions -> Apply Holds

To Retrieve/ Check the holds against order

(B) Actions -> Additional Order Information

Base Tables Affected: oe_hold_sources_all, oe_order_holds_all

Now if we try to book the order you can’t book it will through an error.

To Proceed Further we have to release the hold.

Releasing Holds

(B) Actions -> Release Holds

Base Tables Affected: oe_hold_sources_all, oe_hold_releases_all

Now, you can book the order.

(N) Orders. Returns -> Sales Order

Credit Check

(N) Setup -> Rules -> Credit

Base Tables Affected: oe_credit_check_rules

Assign this credit check rule to the transaction type

(N) Setup -> Transaction Types -> Define

Credit Check Flag For the Customer Profile need to be enabled.

(N) Setup -> Customers -> Profile Classes

We have to check the credit Flag in Payment Terms also.

(N) Receivables -> Setup -> Transaction Types -> Payment Terms

Credit Check Flag need to be checked at 3 places

·         Customer Level

·         Payment Terms

·         Transaction Type

If we create a order which is exceeding the Credit Limit of the customer it will automatically apply the hold.

(N) Orders. Returns -> Sales Order

(B) Actions -> Additional Header Information

To Process this order the hold need to be removed manually.

Cancelling Orders

(N) Orders. Returns -> Sales Order

(B) Actions -> Cancel

Base Tables Affected: oe_order_headers_all, oe_order_lines_all

Purging Of Orders

Create the Purge set and then Query for the Set

(N) Orders. Returns -> Purge -> Order Purge Selection

It’s Time to see the orders through Purge Set

(N) Orders. Returns -> Purge -> Order Purge Selection

Working with Order Organizer

(N) Orders. Returns -> Quick Order Organizer

Create Own Folder

(M) Folders -> New

Check the Tools Available for Organizing Orders

(M) Folders -> Folder Tools

Save the Changes your folder will be created here and if you click the folders button you can see your folder and if you open the folder what ever the changes you made and saved will be reflected.

Changing the prompt appearing in the current form.

(M) Folder -> Change Prompt

Query for some records

You can save the Query for future purposes.

Check the Saved Query in the Public Folders

How does one control Scheduling after booking an Order?

As we have already seen either the lines get automatically scheduled or these are scheduled when the order is booked. But if you want to postpone scheduling still further…

This can be achieved by two ways.
a. Changing of work flow definition
b. Applying hold at the time of order entry

a. Changing of work flow definition

In 11.5.10 release 'Schedule - Line, Deferred' is available to perform scheduling activity with minimal manual intervention.
From Release 12 along with 'Schedule - Line, Deferred' a new sub process 'Schedule - Line, Manual' is introduced.

For example let us consider “Schedule - Line, Deferred” here. Deferred scheduling work flow process to be used in place of the default 'Schedule - Line' activity.

One of the above sub-process can be copied and customized to the generic line process.

The process “Schedule - Line, Deferred” should be used in place of “Schedule – Line” in the Line work flow.

1. Copy “Line Flow – Generic” to some other name for example XX Line Flow -Generic.
2. Copy “Schedule - Line Deferred” sub process into the new work flow process XX Line Flow –Generic as below

3. Replace this with new one.

The New workflow would look as below

Attach this new process to the Transaction Type in question.

Understanding Schedule – Line, Deferred:

Once booking is done then the work flow stops at “Schedule – Deferred” status and this would remain at “Schedule – Deferred” activity. Since this is a deferred activity, we need manual intervention to push this further and this is done using Work flow Background process request.

Sample work flow status diagram for one such case:

Tool --> Work flow Status

The line would wait for an external activity to progress the workflow to Schedule activity.

b. Applying hold at the time of order entry

Apply hold once the line is saved and when booking “Could not Schedule. Line on Hold” processing message pops up. Please apply the hold which is not an workflow activity-specific hold.

Note: If the hold is specific to workflow activity, then the Workflow Activity attached should be 'Line Scheduling' for Workflow Item 'OM Order Line' or Workflow Activity attached should be 'Book Order' for Workflow Item 'OM Order Header'

Sample Test case showing the above

1. Create an Order and apply the hold at the time of order entry.

2. Book the Order following error message pops up

Error message: “Could not Schedule. Line on Hold”

See the Order is Booked but Schedule Ship Date is not populated (Scheduling not done) and Order Line is on Hold.

Work flow status diagram shows

Once the hold is released, do Actions -> Progress Order on the line, or run the Schedule Order concurrent request to schedule the Order.

Note: “OM: Schedule Line on Hold” should be set to No. If the profile is Yes, then the workflow would complete the scheduling activity.

APPLY/RELEASE HOLDS

-----------------------------

HOLDS and RESPONSIBILITIES

Holds can be applied and released by responsibilities.

When you define a hold, you also define which responsibility can apply the hold

and which responsibility can release the hold.

EXAMPLE I:

Create hold called Management Review

Assign Order Management Super User the ability to Apply the hold.

Result: Order Management Super User can Apply the hold, but no one can Release

the hold.

EXAMPLE II:

Create hold called Management Review

Assign Order Management Super User the ability to Release the hold.

Result - No one can Apply the hold and only Order Management Super User can

Release the hold.

Thus, if you are going to assign a responsibility to Apply a hold,

you will need to create a corresponding entry with a responsibility to Release

the hold if you want to Release the hold; if you are going to assign a

responsibilty to Release a hold, you will need to create a corresponding entry

with a responsibility to Apply the hold if you want to Apply the hold.

If you do not assign any responsibility to Apply or Release the hold, then

any responsibility can Apply the hold and any responsibility can Release the

hold.

APPLY HOLDS

In order to Apply a hold, you must define a Hold Source.

A hold source has criteria i.e. specific order number, customer, item, etc.

Additionally, the Hold Source can be applied immediately and to existing/future

orders.

To define a hold source:

1. Navigate to the Apply Holds window by selecting Create Hold Sources from

the Tools menu. The Apply Holds window displays.

2. Select the Name of the hold source in the Criteria tabbed region.

3. Enter the Hold Criteria (up to two criteria can be used)

.....Customer--applies holds source to orders specific customers.

.....Customer Site--applies holds to orders or returns specific customer sites.

.....Warehouse--applies holds to orders or returns specific warehouses.

.....Item--applies holds to orders or returns specific to an item.

.....Order--applies holds to orders or returns specific to an order.

4. Select the Criteria Value.

5. Navigate to the Hold Name tabbed region.

6. Enter the Hold Name of the hold source.

7. Optionally, define the Hold Until Date, which is the date when the hold is

released automatically.

8. Optionally, enter a Hold Comment.

9. Enable the Hold Future Orders/Lines check box to activate the hold later to

new orders and returns that satisfy the hold criteria. Enable the Hold Existing

Orders/Lines check box to activate the hold for existing orders or returns only

that satisfy the hold criteria. Enable both options to place holds on future and

existing orders and returns.

10. Choose the Apply Holds button to create the hold source.

WAYS TO APPLY HOLDS:

A. To apply a hold to a single existing order or return:

1. Navigate to the Sales Orders window and query the order or return you want to

apply the hold.

2. Choose the Actions button and select Apply Hold.

3. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

4. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

5. Optionally, enter a Hold Comment.

6. Choose the Apply Holds button.

B. To apply a hold to multiple orders or returns:

1. Navigate to the Order Organizer window and query the order or return you

want to apply the hold.

2. Multi-select all orders and returns you want to apply the hold.

3. Choose the Actions button and select Apply Hold.

4. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

5. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

6. Optionally, enter a Hold Comment.

7. Choose the Apply Holds button.

C. To apply a hold to a specific order line or return line:

1. Navigate to the Sales Orders window and query the order or return line you

want to apply the hold.

2. Navigate to the Line Items tabbed region and select the order or return line you

want to apply the hold.

3. Choose the Actions button and select Apply Hold.

4. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

5. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

6. Optionally, enter a Hold Comment.

7. Choose the Apply Holds button.

C. To apply a hold to multiple order lines or return lines:

1. Navigate to the Order Organizer window and query the order or return you

want to apply the hold.

2. Navigate to the Line tabbed region.

3. Multi-select the lines you want to apply the hold.

4. Choose the Actions button and select Apply Hold.

5. In the Apply Holds window, select the Hold Name in the Hold Name tabbed

region.

6. Optionally, define the Hold Until Date; that is, the date when the hold is

released automatically.

7. Optionally, enter a Hold Comment.

8. Choose the Apply Holds button.

RELEASE HOLDS

Holds can be released via the Sales Order Window, Find Orders window in the

Order Organizer or via the Release Expired Hold concurrent program.

A. To view or release a hold source:

1. Navigate to the Find Orders window in the Order Organizer.

2. Enter search criteria, including the hold criteria and value or the name of the

hold.

3. Choose the Hold Sources button to query the hold sources that meet your

search criteria.

4. Multi-select the orders or lines that you want to release.

5. Select the Reason for the release.

B. To release a single existing order or return:

1. Navigate to the Sales Orders window and query the order or return you want to

release the hold.

2. Choose the Actions button and select Release Holds.

3. Multi-select the holds that you want to release.

4. Select the release Reason for the hold.

5. Optionally, enter a Comment.

6. Choose the Release button.

7. Save your work.

C. To release a specific order line or return line:

1. Navigate to the Sales Orders window and query the order or return line you

want to release.

2. Navigate to the Line Items tabbed region and select the order or return line you

want to release.

3. Choose the Actions button and select Release Holds.

4. Multi-select the holds that you want to release.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

D. To release multiple orders or returns:

1. Navigate to the Orders Organizer window and query the order or return you

want to release.

2. Multi-select all orders and returns you want to release.

3. Choose the Actions button and select Release Holds.

4. Multi-select the holds that you want to release.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

E.  To release multiple order lines or return lines:

1. Navigate to the Orders Organizer window and query the order or return you

want to apply the hold.

2. Navigate to the Line Items tabbed region.

3. Multi-select the lines you want to release.

4. Choose the Actions button and select Release Holds.

5. Enter the Release name.

6. Select the Reason for the release.

7. Optionally, enter a Comment.

8. Choose the Release button.

9. Save your work.

Additionally, Order Management provides the ability to release all expired holds

by effectivity date. The concurrent program is called Release Expired Hold; there

are no enterable input parameters for this program. When the concurrent program,

Release Expired Hold, is called, all expired holds with an ending Effectivity

Date less than or equal to the system date will automatically be released.

HOLD HISTORY

To view hold history:

1. Navigate to the Sales Orders window and query the order or return you want to

view.

2. Choose the Actions button and select Additional Order Information.

3. Select the Holds tabbed region to view hold history information.

The Additional Order Information window displays the hold history

information. The window displays all order level and line level holds for the

order.

.....For an order level hold, the At field will display the text Order.

Applying Holds

.....For a line level hold, the At field will display order line number.

RELATED DOCUMENTS

-----------------

Oracle Order Management User’s Guide for Release 11i User’s Guide,

Part No. A7702

FREQUENTLY ASKED QUESTIONS

-----------------------------

1. What are the Item Attributes that affect Drop Ship orders?

Inventory: Transactable

Purchasing: Purchased, Purchasable, List Price (must be entered)

Receiving: Receipt Routing set to direct

Order Entry: Customer Ordered, Customers Enabled, OE Transactable, Shippable

NOTE: With family Pack G and lower only standard items can be drop shipped.

With family pack H and higher, kits and models can be drop shipped.

Please review metalink note 113636.1 for item setup.

2. How do I drop ship across operating units?

Release 11i does not currently support this functionality.

3. How are over/under shipments handled in drop shipment?

If part of a drop-ship line ships and you do not wish to fulfill the

remaining quantity, cancel the line. If the quantity shipped is less than

the quantity ordered, the sales order line will be split into two lines. The

first line will display the quantity shipped and the second line will display

the backordered quantity. Over-shipments must also be handled manually. If the

Supplier ships more than the ordered quantity, you can bill your customer for

the additional quantity or request that they return the item.

Use the Sales Order and Purchase Order Discrepancy Report to view differences

between your drop-ship sales orders and their associated purchase

requisitions and orders.

4. When the Requisition Import parameter 'Initiate Approval after Import' is

set to 'No', the requisition still gets imported in an approved status.

Why is this?

The Purchase Release program in the OM module ignores the setting of this

parameter. When it is populating the data in the po_requisitions_interface

table the field authorization_status is hardcoded with the value 'APPROVED'.

Thus in the case of drop shipments, the requisition always gets imported in

an approved status. The reason for this is because the sales order has

already been 'purchase released'.

5. Did any drop-ship functionality change from R11 to R11i?

In R11i drop shipping is Workflow enabled. The OM Order Line Workflow calls

the 'Create Supply - Line' process. This process has a function called

'Branch on Source Type' which will detect an item with a Source Type of

'External' and set the line to a 'Purchase Release - Deferred' status.

Then when the Workflow Background processor picks up the line, it will

initiate the Purchase Release process to write the records to the interface

table. After the Requisition Import is successfully run, the sales order

line status will change to 'Awaiting Receipt'.

Another big change in R11i is in the way locations are maintained. You no

longer need to define a separate Purchasing Location for each customer

Ship-To location, as was necessary in R11. Purchasing now uses a view a the

HZ and HR tables to derive the locations.

6. How can I view the Purchasing information for a drop ship order?

After the requisition import successfully pulls in the data from the

PO_REQUISITIONS_INTERFACE_ALL table, and creates the internal requisition,

this information is available from the Sales Orders form as follows:

Select the sales order line with the externally sourced item and hit

Actions -> Additional Line Information

Select the Drop Ship tab to view the data

7. Does the Purchase Release concurrent program still exist?

Yes, this program can be run from the Orders, Returns -> Purchase Release

menu. If there are holds which prevent the order from progressing through

the Workflow normally after the order is booked, these holds must be

manually released. Then the externally sourced lines can be sent to

Purchasing by running the concurrent program. Alternatively, you can use

the Actions -> Progress Order button to push the lines through to the

PO_REQUISTIONS_INTERFACE_ALL table for Requisition Import to pick them up.

8. Can you drop ship kits?

You can drop ship kits in family pack H and higher.

9. You are trying to split a line on a drop ship order line after purchase

release; you receive the message:

You are not allowed to split order line because Line is released to purchasing

SO AND CUSTOMER AND RECEIPTS RELATED QUERIES

--- find OUT the customer, line item, ordered qty AND price info OF the ORDER :

SELECT h.order_number, org.NAME customer_name, h.ordered_date order_date,

ot.NAME order_type, s.NAME sales_rep, l.line_id, l.line_number,

l.inventory_item_id, si.segment1, l.ordered_quantity,

l.unit_selling_price,

NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,

h.transactional_curr_code currency_code

FROM ra_salesreps s,

oe_transaction_types_tl ot,

oe_sold_to_orgs_v org,

mtl_system_items_vl si,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND l.header_id = h.header_id

AND h.sold_to_org_id = org.organization_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND l.open_flag = 'Y'

AND l.service_reference_line_id IS NULL

AND l.inventory_item_id = si.inventory_item_id

AND NVL (si.organization_id, 0) = 204 --Item master orgn

AND h.order_type_id = ot.transaction_type_id

AND h.salesrep_id = s.salesrep_id

AND h.org_id = s.org_id

ORDER BY l.line_id

/

--find customer, ship TO AND bill TO information OF an ORDER :

SELECT h.order_number, c.NAME customer_name, lk1.meaning freight_terms,

lk2.meaning fob, s.location_code ship_location_code,

s.address_line_1 ship_address1, s.address_line_2 ship_address2,

s.state ship_state, s.postal_code ship_zip, s.country ship_country,

b.location_code bill_location_code, b.address_line_1 bill_address1,

b.address_line_2 bill_address2, b.country bill_country

FROM ar_lookups lk2,

oe_lookups lk1,

oe_sold_to_orgs_v c,

oe_invoice_to_orgs_v b,

oe_ship_to_orgs_v s,

oe_order_headers_all h

WHERE h.order_number = '123'

AND h.org_id = '204'

AND h.ship_to_org_id = s.organization_id

AND h.invoice_to_org_id = b.organization_id

AND h.sold_to_org_id = c.organization_id

AND h.freight_terms_code = lk1.lookup_code(+)

AND lk1.lookup_type(+) = 'FREIGHT_TERMS'

AND lk2.lookup_code(+) = h.fob_point_code

AND lk2.lookup_type(+) = 'FOB'

/

-- find OUT ORDER AND line hold information :

SELECT ho.NAME hold_name, hs.hold_until_date, hs.hold_comment,

h.order_number, oh.header_id, oh.line_id, oh.order_hold_id,

l.item_identifier_type, l.inventory_item_id, l.ordered_item

FROM oe_order_holds_all oh,

oe_order_lines_all l,

oe_order_headers_all h,

oe_hold_definitions ho,

oe_hold_sources_all hs

WHERE h.order_number = '1234'

AND oh.header_id = h.header_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND oh.hold_source_id = hs.hold_source_id

AND hs.hold_id = ho.hold_id

AND h.header_id = l.header_id(+)

AND l.open_flag = 'Y'

AND l.line_id = NVL (oh.line_id, l.line_id)

AND l.service_reference_line_id IS NULL

AND oh.hold_release_id IS NULL

AND NVL (h.org_id, 0) = '204'

AND NVL (l.org_id, 0) = NVL (h.org_id, 0)

ORDER BY ho.NAME, h.order_number

/

---find freight related info OF ORDER viz: freight carrier, ship method AND service LEVEL :

SELECT h.order_number, h.shipping_method_code, wc.carrier_name,

wcsm.service_level, wcsm.freight_code

FROM wsh_carrier_ship_methods_v wcsm,

wsh_carriers_v wc,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND h.shipping_method_code = wcsm.ship_method_code(+)

AND NVL (wcsm.organization_id(+), 0) = 204 --Master Organization

AND wcsm.freight_code = wc.freight_code(+)

ORDER BY h.order_number

/

--find price discounts AND surcharges ON ORDER lines :

SELECT h.order_number, l.line_number, pa.list_line_type_code,

pa.arithmetic_operator, pa.operand,

DECODE (pa.modifier_level_code,

'ORDER', l.unit_list_price

* l.ordered_quantity

* pa.operand

* SIGN (pa.adjusted_amount)

/ 100,

(pa.adjusted_amount * NVL (l.ordered_quantity, 0))

) discount_amt

FROM qp_list_headers_vl lh,

oe_price_adjustments pa,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = '12345'

AND h.header_id = l.header_id

AND h.org_id = l.org_id

AND h.header_id = pa.header_id

AND l.line_id = pa.line_id(+)

AND pa.list_header_id = lh.list_header_id

AND ( pa.list_line_type_code = 'DIS'

OR pa.list_line_type_code = 'SUR'

OR pa.list_line_type_code = 'PBH'

)

AND pa.applied_flag = 'Y'

AND NOT EXISTS (

SELECT 'X'

FROM oe_price_adj_assocs pas, oe_price_adjustments pa1

WHERE pas.rltd_price_adj_id = pa.price_adjustment_id

AND pa1.price_adjustment_id = pas.price_adjustment_id

AND pa1.list_line_type_code = 'PBH')

ORDER BY l.line_id

/

a: Qp_list_headers_vl IS VIEW based ON qp_list_headers_b AND qp_list_headers_tl TABLES.

-- find freight charges ON ORDER lines :

SELECT header_id, line_id, charge_id, charge_name, charge_amount,

currency_code, invoiced_flag, interco_invoiced_flag, org_id,

source_system_code, estimated_flag, invoiced_amount

FROM oe_charge_lines_v

WHERE header_id = (SELECT header_id

FROM oe_order_headers_all

WHERE order_number = '12345')

ORDER BY line_id

/

a: The OE_CHARGE_LINES_V VIEW IS based ON oe_price_adjustments, oe_order_headers_all ANDoe_order_lines_all FOR FREIGHT CHARGES.

---SALES tax rate FOR State 'xx' :

SELECT DISTINCT lv.parent_segment_id, lc.location_id_segment_1,

location_segment_user_value, lr.from_postal_code,

lr.to_postal_code, location_segment_value, lr.tax_rate

FROM ar_location_rates lr,

ar_location_combinations lc,

ar_location_values lv

WHERE lv.location_segment_user_value = 'xx' --State name

AND lv.location_segment_id = lc.location_id_segment_1

AND lv.location_structure_id = lc.location_structure_id

AND lc.location_structure_id = '101'

AND lv.location_segment_id = lr.location_segment_id

ORDER BY 1

/

---TABLE ar_sales_tax contains location wise total tax- rates WITH tax break up ..

SELECT DISTINCT location_id, rate_context, tax_rate, location1_rate,

location2_rate, location3_rate, from_postal_code,

to_postal_code

FROM ar_sales_tax

WHERE location_id = 1000 AND enabled_flag = 'Y'

/

-- find OUT the shipper info :

/* Formatted on 2010/08/24 11:32 (Formatter Plus v4.8.0) */

SELECT wnd.delivery_id delivery_id, SUBSTRB (party.party_name, 1,

50) customer,

wpb.NAME batch_name,

wsh_util_core.get_location_description

(wnd.initial_pickup_location_id,

'NEW UI CODE'

) ship_from,

wsh_util_core.get_location_description

(wnd.ultimate_dropoff_location_id,

'NEW UI CODE'

) ship_to,

wnd.initial_pickup_date pickup_date,

wnd.ultimate_dropoff_date dropoff_date, lv.meaning ship_method,

wnd.waybill waybill, wnd.gross_weight gross_weight,

wnd.weight_uom_code uom, wnd.status_code, we.MESSAGE

FROM wsh_new_deliveries wnd,

wsh_picking_batches wpb,

wsh_exceptions we,

fnd_lookup_values_vl lv,

hz_cust_accounts cust_acct,

hz_parties party

WHERE wnd.delivery_id = '123'

AND wpb.batch_id = wnd.batch_id

AND we.delivery_id(+) = wnd.delivery_id

AND we.exception_name(+) = 'WSH_BATCH_MESSAGE'

AND lv.lookup_code(+) = wpb.ship_method_code

AND lv.lookup_type(+) = 'SHIP_METHOD'

AND lv.view_application_id(+) = '1'

AND cust_acct.cust_account_id(+) = wnd.customer_id

AND party.party_id(+) = cust_acct.party_id

/

-- find OUT shipper detail info :

SELECT wnd.delivery_id, wnd.NAME delivery_name,

wdd.source_header_number so_order_number,

oola.line_number so_line_number, wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id, wdd.shipping_instructions,

wdd.inventory_item_id, wdd.requested_quantity_uom,

msi.description item_description, msi.revision_qty_control_code,

wdd.ship_method_code carrier, wdd.shipment_priority_code priority,

wdd.organization_id, wnd.initial_pickup_location_id,

wdd.released_status, wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id = '123'

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

---find OUT Move ORDER line details :

SELECT wnd.delivery_id, wnd.NAME delivery_name,

wnd.initial_pickup_location_id, mtrh.request_number mo_number,

mtrl.line_number mo_line_number, mtrl.line_id mo_line_id,

mtrl.from_subinventory_code, mtrl.to_subinventory_code,

mtrl.lot_number, mtrl.serial_number_start, mtrl.serial_number_end,

mtrl.uom_code, mtrl.quantity, mtrl.quantity_delivered,

mtrl.quantity_detailed, wdd.source_header_number so_order_number,

oola.line_number so_line_number, wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id, wdd.shipping_instructions,

wdd.inventory_item_id, wdd.requested_quantity_uom,

msi.description item_description, msi.revision_qty_control_code,

wdd.ship_method_code carrier, wdd.shipment_priority_code priority,

wdd.organization_id, wdd.released_status, wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

mtl_txn_request_lines mtrl,

mtl_txn_request_headers mtrh,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id = '123'

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.move_order_line_id = mtrl.line_id

AND mtrl.header_id = mtrh.header_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

--- find Bill OF Lading info OF the Delivery :

SELECT wnd.delivery_id delivery_id, wdi.sequence_number bol_number,

wdi.bol_notify_party, wdi.port_of_loading, wdi.port_of_discharge,

wnd.waybill waybill, wnd.gross_weight gross_weight,

wnd.weight_uom_code uom, wnd.status_code

FROM wsh_new_deliveries wnd,

wsh_delivery_legs wdl,

wsh_document_instances wdi

WHERE wnd.delivery_id = '123'

AND wnd.delivery_id = wdl.delivery_id(+)

AND wdi.entity_id(+) = wdl.delivery_leg_id

AND wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'

AND wdi.document_type(+) = 'BOL'

AND wdi.status(+) <> 'CANCELLED'

/

--- find delivery leg AND pick up STOP info :

SELECT wt.trip_id, wt.NAME, wt.status_code, wt.vehicle_item_id,

wt.vehicle_number, wt.carrier_id, wt.ship_method_code, wts.stop_id,

wts.stop_location_id, wts.status_code, wts.stop_sequence_number,

wts.planned_arrival_date, wts.planned_departure_date,

wts.actual_arrival_date, wts.actual_departure_date,

wts.departure_net_weight, wts.weight_uom_code, wdl.delivery_leg_id,

wdl.delivery_id, wdl.pick_up_stop_id, wdl.drop_off_stop_id,

wdl.sequence_number, wdl.loading_order_flag, wdl.shipper_title,

wdl.shipper_phone

FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl

WHERE wdl.delivery_id = '123'

AND wts.stop_id = wdl.pick_up_stop_id

AND wts.trip_id = wt.trip_id;