Playing with Oracle Configurator,Creating Model items in Sales Order through API

This is to create multiple PTO (or ATO) Items in Sales Orders through an API.
This is a very often requirement to any client.
Pre-requisites:
1) A BOM should be defined for that Model Item.

Steps:
1) Get the Configurator ID and Revision number for that model item.
2) Populate the headers level records for the API Call.
/*****************INITIALIZE HEADER RECORD******************************/ l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_source_id := l_order_source_id; l_header_rec.orig_sys_document_ref := rec_stg_hdr_all.category_room; l_header_rec.order_type_id := l_trans_type_id;
l_header_rec.sold_to_org_id := l_cust_account_id;
l_header_rec.price_list_id := l_price_list_id;
l_header_rec.invoice_to_org_id := l_bill_id;
l_header_rec.ship_to_org_id := l_ship_id;
l_header_rec.salesrep_id := g_salesrep_id;
l_header_rec.ordered_date := SYSDATE;
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.created_by := g_user_id;
l_header_rec.creation_date := SYSDATE;
l_header_rec.last_update_date := SYSDATE;
l_header_rec.transactional_curr_code := g_trans_curr_code;
l_header_rec.org_id := g_org_id;

3) Assiging Line items:
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(l_count) := oe_order_pub.g_miss_request_rec; --****************INITIALIZE LINE RECORD******************************** l_line_tbl (l_count) := oe_order_pub.g_miss_line_rec;
l_line_tbl (l_count).orig_sys_document_ref := rec_stg_hdr.category_room;
l_line_tbl (l_count).orig_sys_line_ref := l_count;
l_line_tbl (l_count).inventory_item_id := rec_stg_hdr.top_model_item_id;
l_line_tbl (l_count).request_date := SYSDATE;
l_line_tbl (l_count).line_number := l_count;
l_line_tbl (l_count).ordered_quantity := 1;
l_line_tbl (l_count).operation := oe_globals.g_opr_create;
l_line_tbl (l_count).tax_exempt_flag := g_tax_exempt_flag;
l_line_tbl (l_count).config_header_id := p_config_header_id;
l_line_tbl (l_count).item_type_code := g_item_type_code;
l_line_tbl (l_count).config_rev_nbr := p_config_rev_nbr;
l_line_tbl (l_count).top_model_line_index := l_count;
l_line_tbl (l_count).link_to_line_index := NULL;
l_line_tbl (l_count).created_by := g_user_id;
l_line_tbl (l_count).creation_date := SYSDATE;
l_line_tbl (l_count).last_update_date := SYSDATE;
l_line_tbl (l_count).line_type_id := l_line_trans_type_id;

The intersting here is
we need to populate the two columns while creating with the API.
l_line_tbl (l_count).top_model_line_index := l_count;
l_line_tbl (l_count).link_to_line_index := NULL;

Drop Shipment in 12

Drop shipment feature is slightly enhanced in R12.
In Drop shipment scenario the supplier directly ship the goods to the requested client.
In R12 there is a column PO_LINE_LOCATIONS_ALL.DROP_SHIP_FLAG which will be set to 'Y' if that PO is a drop ship PO.Otherwise it is null.
Also in the Purchase Order screen they have added one Tab called Drop Ship in the Shipmets Window.There you can see order number ,line number, customer name, and ship to location etc.

It is avery desired feature to know whcih PO is a drop ship or not.

Cheers
Vyaghresh

Runnng a Conc.Program from Unix command

Now i turned my side towards Unix Scripts and its relation to Oracle Apps projects.
Yes we can submit concurrent request directly from Unix command prompt.
Most of the unix lovers will have it handy.
The below piece can be useful.
$FND_TOP/bin/CONCSUB APPS/APPS \
SYSADMIN \
"System Administrator" \
VYAGHRESH \
WAIT=N \
CONCURRENT \
XXApplshortname \
XXcocnprogshortname \
param1 param2

where XXApplshortname is the Short name of the Conc Program
XXcocnprogshortname is the Short name of the conc. program we are running
Param1 and Param2 are the parameters for the program we are running.
We can sent any no.of parameters in this way.

Upon successful submission it will give the Request_id.

PO Update APIs po_change_api1_s.update_po

It is quite often to update some of the lines of a PO after it is approved.
In order to Update a PO, we can use the API po_change_api1_s.update_po.

It Update a standard purchase order or release changes of quantity, price, promise date and Need-by-date.This Need-by-date updation with this API has been enhanced in R12 only. In earlier releases updating the PO line’s Need-by-date is not available.

Psudo code of the usage
l_result := po_change_api1_s.update_po
(x_po_number => p_po_num ,
x_release_number => NULL ,
x_revision_number => l_revision_no,
x_line_number => lines_rec.line_num,
x_shipment_number => lines_rec.shipment_num,
new_quantity => NULL,
new_price => NULL,
new_promised_date => NULL ,
new_need_by_date => TO_DATE(nedbydate,'DD-MON-RRRR'),
launch_approvals_flag => 'N' ,
update_source => NULL,
VERSION => '1.0',
x_api_errors => l_api_errors,
p_buyer_name => Lines_rec.agent_name,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => g_org_id);

It will either return a 1 for successful update or 0 for failure.

Pre-requisites to use this API
1) PO header to be either in APPROVED or REQUIRES REAPPROVAL status.
2) No update if the revision number doesn't match the current revision.
Post Update
1)Upon successful updation,the PO revision will be incremented.
2) if launch_approvals_flag is set to 'Y' then it will launch the PO Approval. But if you set this parameter as 'Y' then the program try to submit the PO Approval for every successful update of the line.Most of the time this Approval will not update the PO to APPROVED, it may be delay waiting for some other input, so it will make the PO status as INPROCESS.
So please avoid to launch the Po Approval with this Update PO API.
3)The authorization status of the PO will be in REQUIRES REAPPROVAL is the above paramter is set to 'N'

Difficulties faced:
1) This will update the PO if it have one line without any issue in the first run.
2) If the PO have more than one line, then we need to run the Update program the no.of lines po have.We need to pass the Po line num for every run.
3)If the first PO line got updated then revision will gets chnaged to next highest number.So we need to grab the revision number for every updated line and need to input to the above program.
4) By doing this we can avoid the weird error 'The revision number doesn't match the current revision'.
5)Need to launch the PO approval manually because it will leave teh PO in REQUIRES REAPPROVAL state.

To Find the Sales Order's Subtotal,discount,charges and Tax

It is quite common to find the Sales orders Total amount distributions such as Sub total (Line Total),Discount,Tax and Charges if any.

These distribution amounts will not be the straight database columns in the Order Management tables.
In order to find these need to explore the API.i.e.

OE_OE_TOTALS_SUMMARY.ORDER_TOTALS(
header_id In,
subtotal,out,
discount out,
charges out,
Tax out);

Total amunt of the Order=Subtotal+charges+Tax.

Auditing Enabling for the Standard Tables.

Most of the time we may asked to get the previous values stored in any seeded or custom tables for auiting purpose; Eg. IF a PO is created with a Unit Price of $100 and then somebody updated to $120 then if you were asked to get the old value i.e $100. We have to follow the below steps for enabling the audit for the table po_line_all for the column unit_price. Oracle will create a table like po_lines_all_a internally and it will store all the audit records with the Primary key column and the auditing column and a sequence id; For the above transaction this table contains two records With transaction_type as ‘I’ (Insert) and unit_price column would be blank. And transaction_type as ‘U’ (Update) and unit_price column would be 100. (The old value). 1)Goto System Administrtor->Audit Trail -> Tables -> Query for Define an Application User.let say you are suppose to do the audit on these tables. PO_LINES_ALL 2)Add the columns whatever you want to audit. 3)Go to Audit Trail -> Groups -> create a new group. Enable the ‘Audit Setup Group’. 4)Run Audittrail Update Table. Add the application user table under Audit Setup Group. 5)Run Audittrail Update Table. 6)Go to Audit Query Navigator ->Functional Groups-> Now you can see the Define an Application user table added under the audit setup group. 7)If the table is available as per the step 6,Now run the Audit trail report,you will be able to get the audit information.

How to make a Report Concurrent Program to end up a Warning state.

It is pretty easy to make a PLSQL Stored procedure Concurrent Program's status to Warning by assigning the value '1' to the Ret_code Out variable.

In the case of reports, we can use
declare
var boolean;
if :cs_count >10000 then
var:=fnd_concurrent.set_completion_status('WARNING','Report will show the warning state now');
end if;
The above package will return a Boolean value TRUE if it successfully completes with a Warning.
You can use it in the Before or After report Trigger or anywhere in the report trigger depends on the requirement.