, , ,

Reprocessing Period Close Pending Transactions


Unprocessed Transaction Messages While Closing the Inventory Accounting Period

While closing the inventory accounting periods, If there are unprocessed transactions, then one of the following messages appears:

Pending receiving transactions for this period
When you use Purchasing, this message indicates you have unprocessed purchasing transactions in the RCV_TRANSACTIONS_ INTERFACE table. These transactions include purchase order receipts and returns for inventory. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your receiving value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Unprocessed material transactions exist for this period

This message indicates you have unprocessed material transactions in the MTL_MATERIAL_TRANSACTIONS_TEMP table. You are unable to close the period with this condition. Please see your system administrator. Inventory considers entries in this table as part of the quantity movement.

Closing the period in this situation is not allowed because the resultant accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Pending material transactions for this period

This message indicates you have unprocessed material transactions in the MTL_TRANSACTIONS_INTERFACE table. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your inventory value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Uncosted material transactions exist for this period

This message indicates you have material transactions in the MTL_MATERIAL_TRANSACTIONS table with no accounting entries (Standard Costing) and no accounting entries and no costs (Average Costing). You are unable to close the period with this condition. These transactions are part of your inventory value.

Closing the period in this situation is not allowed because the resultant accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Pending move transactions for this period

This message indicates you have unprocessed shop floor move transactions in the WIP_MOVE_TXN_INTERFACE table. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your work in process value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Pending WIP costing transactions exist in this period

This message indicates you have unprocessed resource and overhead accounting transactions in the WIP_COST_TXN_INTERFACE table. You are unable to close the period with this condition. These transactions are in your work in process value, and awaiting further processing.

Closing the period in this situation is not allowed because the resulting accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Reprocessing Period Close Pending Transactions:

There are a variety of reasons for pending transactions, which we have discussed above. This following document will serve as a guide for troubleshooting and processing pending transactions preventing an accounting period from being closed.

When resolving and working with Pending Transactions users must collect and identify data in order to address the source product and complete the Period Close process.

Hence the key steps for resolving pending transactions are:

– Locate the transactions
– Find the error message to determine what is preventing the transactions from processing.
– Resolve the error
– Resubmit the pending record.

/*SCRIPT TO IDENTIFY PENDING TRANSACTIONS & STEPS TO REPROCESS THEM
================================================================*/

— Pending Move Transactions

select mti.*
from wip_move_txn_interface mti,
org_organization_definitions org
where mti.organization_id = org.organization_id
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select organization_code,count(*)
from wip_move_txn_interface mti
group by organization_code

select mti.organization_id,mti.organization_code,org.organization_name,count(*)
from wip_move_txn_interface mti,
org_organization_definitions org
where mti.organization_id = org.organization_id
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mti.organization_id,mti.organization_code,org.organization_name

select mti.organization_id,mti.organization_code,org.organization_name,count(*) from
wip_move_txn_interface mti,
wip_txn_interface_errors emsg,
mtl_system_items msi,
org_organization_definitions org
where mti.transaction_id = emsg.transaction_id
and mti.primary_item_id = msi.inventory_item_id(+)
and mti.organization_id = msi.organization_id(+)
and mti.organization_id = org.organization_id
and mti.process_status = 3
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mti.organization_id,mti.organization_code,org.organization_name

— Script to Reprocess Pending Move Transactions
update wip_move_txn_interface
set group_id=null,
request_Id = null,
process_status=1,
transaction_id=null
where process_status=3
and Transaction_id = &Transaction_id — Enter the transaction_id which you want to reprocess

–Pending Resource Transactions

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3

select wct.organization_id,wct.organization_code,wct.process_status,org.organization_name,count(*)
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3
group by wct.organization_id,wct.organization_code,wct.process_status,org.organization_name

select wcti.organization_code,wtie.error_message,wtie.error_column,wcti.transaction_id,
wcti.transaction_date,wcti.creation_date,wcti.process_phase,wcti.process_status,
we.wip_entity_name,msi.segment1,wcti.operation_seq_num,wcti.resource_seq_num,
wcti.transaction_quantity,wcti.transaction_uom,wcti.primary_uom,wcti.move_transaction_id
from wip_cost_txn_interface wcti,
wip_txn_interface_errors wtie,
wip_entities we,
mtl_system_items msi
where wcti.organization_id = msi.organization_id
and wcti.organization_id = we.organization_id
and wcti.primary_item_id = msi.inventory_item_id
and wcti. wip_entity_id = we.wip_entity_id
and wcti.transaction_id = wtie.transaction_id
and trunc(wcti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wcti.process_status = 3
order by wcti.organization_code

— Script to reprocess Pending Resource Transactions
update wip_cost_txn_interface
set group_id=NULL,
transaction_id = NULL,
process_status= 1
where process_status = 3
and Transaction_id = &Transaction_id — Enter the transaction_id which you want to reprocess

— Transaction Open Interface

select mti.*
from mtl_transactions_interface_v mti,
org_organization_definitions org
where mti.organization_name = org.organization_name
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mti.creation_date,mti.process_flag,mti.process_flag_desc,mti.error_explanation,
mti.error_code,mti.transaction_interface_id,mti.transaction_header_id,
mti.source_code,mti.transaction_source_type_name,mti.transaction_type_name,mti.source_header_id,
mti.source_line_id,mti.transaction_mode,mti.transaction_mode_desc,mti.organization_id,
mti.organization_code,mti.organization_name,mti.inventory_item_id,mti.transaction_source_id
from mtl_transactions_interface_v mti,
org_organization_definitions org
where mti.organization_name = org.organization_name
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and process_flag = 3
order by organization_id,organization_code,organization_name

select mti.organization_id,mti.organization_code,mti.organization_name,mti.process_flag,count(*)
from mtl_transactions_interface_v mti
where trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and mti.process_flag = 3
group by organization_id,organization_code,organization_name,mti.process_flag

— Script to Process the errored Records from Transactions Open Interface
update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = 1,
error_code = null,
error_explanation = null
where organization_id = &Organization_id
and process_flag = 3
and transaction_interface_id = &transaction_interface_id

— Pending Material Transactions

select * from mtl_material_transactions_temp
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mmtt.*
from mtl_material_transactions_temp mmtt,
org_organization_definitions org
where mmtt.organization_id = org.organization_id
and trunc(mmtt.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mmtp.organization_id,org.organization_code,org.organization_name,count(*)
from mtl_material_transactions_temp mmtp,
org_organization_definitions org
where mmtp.organization_id = org.organization_id
and trunc(mmtp.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mmtp.organization_id,org.organization_code,org.organization_name

select count(*),mmtt.error_code,mmtt.error_explanation,org.organization_id,org.organization_code,org.organization_name,org.operating_unit
from mtl_material_transactions_temp mmtt,
org_organization_definitions org
where org.organization_id = mmtt.organization_id
and trunc(mmtt.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mmtt.error_code,mmtt.error_explanation,org.organization_id,org.organization_code,org.organization_name,org.operating_unit
order by org.organization_id

— Script to Reprocess the Pending Material Transactions

update mtl_material_transactions_temp
set process_flag = ‘Y’,
lock_flag = ‘N’,
transaction_mode = 3,
error_code = NULL,
error_explanation = NULL
where process_flag in (‘Y’,’E’)
and organization_id = &Organization_id
and transaction_temp_id = &transaction_temp_id

— Count Of Uncosted Transactions
select * from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null

select costed_flag,count(*)
from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null
group by costed_flag

select costed_flag,organization_id,acct_period_id,count(*)
from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null
and costed_flag = ‘E’
group by costed_flag,organization_id,acct_period_id

— Script to Reprocess the Costed Transactions

update mtl_material_transactions
set costed_flag = ‘N’,
transaction_group_id = NULL,
transaction_set_id = NULL,
request_id = NULL,
error_code = NULL,
error_explanation = NULL,
where (costed_flag = ‘E’ or costed_flag = ‘N’)
and transaction_id = &transaction_id

— Check for the Shipping Transaction Stuck in the Inventory Interface

SELECT wdd.delivery_detail_id, oe_interfaced_flag, inv_interfaced_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_parameters mp
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = ‘Y’
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = mp.organization_id
AND mp.organization_code = ‘A66’ — Enter The Organization_code

SELECT wts.stop_id, wts.pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_parameters mp
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = ‘Y’
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = mp.organization_id
AND mp.organization_code = ‘A66’ — Enter The Organization_code
SELECT
wdd.source_header_id header_id,
ooh.order_number,
ool.line_number,
ool.shipment_number,
ool.line_id,
wnd.delivery_id,
wnd.NAME delivery,
wdd.delivery_detail_id,
wdl.pick_up_stop_id,
wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE wdd.source_code = ‘OE’
AND wdd.released_status = ‘C’
AND wdd.inv_interfaced_flag IN (‘N’, ‘P’)
AND wdd.organization_id = &organization_id — Enter The Organization_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.status_code IN (‘CL’, ‘IT’)
AND wdl.delivery_id = wnd.delivery_id
AND TRUNC (wts.actual_departure_date) BETWEEN ’01-AUG-2009′ AND ’31-AUG-2009′
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id

— Steps to reprocess the pending shipping transactions

Verify that there are NO records for this Sales Order in the Pending Transactions Form or the Transaction Open Interface Form. Address the errors if any.
The records retrieved in these forms will list the Sales Order Number under the “Source” or “Transaction Source”columns for the Source TAB respectively.
Navigation> Inventory> Transactions> Pending Transactions
Navigation> Inventory> Transactions> Transaction Open Interface

For records with WSH_DELIVERY_DETAILS.OE_INTERFACED_FLAG or WSH_DELIVERY_DETAILS.INV_INTERFACED_FLAG values “P”,
please run the Interface Trip Stop process in Order Management to complete workflow for the Sales Order.
Navigation> Order Management> Shipping> Interfaces> Run > Select the Interface Trip Stop – SRS.

/by
, , , ,

Oracle Manufacturing Concepts

DISCRETE MANUFACTURING
Discrete manufacturing is a manufacturing process in which distinct items/products(which you can easily count, see and touch) are built or manufactured in discrete batches on manufacturing floor. It creates physical products which go directly to business and consumers, and assemblies that are used by other manufacturers. The resulting product is easily identifiable. It is different from process manufacturing where products are undifferentiated (can not tell the difference between one product and another) such as oil, natural gas and salt.

A typical characteristic of discrete manufacturing is the frequent switching from one manufactured product to another. The products are typically manufactured in individually defined lots, the sequence of work centers through production varying for each one of these. Costs are calculated on the basis of orders and individual lots

Discrete manufacturing is also characterized by individual or separate unit production. Units can be produced in low volume with very high complexity or high volumes of low complexity. Low volume/high complexity production results in the need for an extremely flexible manufacturing system that can improve quality and time-to-market speed while cutting costs. High volume/low complexity production puts high premiums on inventory controls, lead times and reducing or limiting materials costs and waste

Discrete manufacturing typically involves the sequence of work centers through which the products can pass during production. This sequence can be varied as per requirement. The order of work centers is determined in routing’s, which can often be very complex. There can be waiting times between the individual work centers. Also, semi-finished products are frequently placed in interim storage prior to further processing

Examples like Transportation equipment, Automobiles, toys, Computer and accessories and electronic products, consumer electronics, furniture, Lego Blocks, Appliances and other house hold items, Industrial and electrical equipment, Medical equipment and supplies, Fabricated metal, furniture, recycling, pencil ,light bulb, telephone, bicycle, Fuel Pump etc…

PROCESS MANUFACTURING
Process manufacturing is different from Discrete manufacturing. Manufacturing is not in discrete batches but is a process of pressing/mixing/chemical processing/heating/boiling liquid/semi liquid/solid and powder or raw materials. Once you manufacture a product by using process manufacturing, the output can not be brought to it’s original basic form

For example orange juice with sugar added cannot be put back in to the Orange and Sugar separately. On the other hand a computer manufactured by a discrete manufacturing process can be disassembled and the parts can be returned to stock to a large extent. Examples of process manufacturing are food products, beverages, paints & coatings, chemicals, specialty chemicals , pharmaceuticals, consumer packaged goods, Bulk drug pharmaceuticals, Nutraceutical, cosmeceutical and biotechnology industries. In Process Manufacturing, there are ingredients and not parts; there are formulas and not bill of materials; and bulk, not Unit of measure Each

FLOW MANUFACTURING

Flow Manufacturing is an innovative manufacturing method which synchronizes production with customer demand

Oracle Flow Manufacturing module supports the entire build-to-order manufacturing process which includes make-to-stock, configure-to-order, discrete-repetitive, assemble-to-order, and engineer-to-order manufacturing strategies and methods. It initiates schedules as soon as customer orders are received, and ensures shipment as soon as build is complete. Flow manufacturing employs pulls material using kanbans planning and back flushes material and costs upon completion. This in turn helps decrease inventories, optimize machine utilization, reduce response time to customer orders, and simplify shop floor activities.

Flow manufacturing production lines are designed to support the inter-mixed production of multiple products within a family on the same line at a constant rate. It can be used in Inventory module to replenish kanbans and in Work in Process to complete assemblies without having to create a job or a schedule (work order less job)

PROJECT MANUFACTURING
Large contracts or projects received by the companies can not be completely fulfilled by process manufacturing or discrete or repetitive manufacturing methods. It requires a separate manufacturing method known as project manufacturing. Project manufacturing meets demand driven production requirements for large contracts or projects. It allows you to plan, schedule, process and cost against a specific contract or a group of contracts or project for a specific customer.

Oracle Project Manufacturing supports companies in the Engineer-To-Order, Make-To-Order manufacturing strategies and Aerospace and Defense industries. These industries plan, track, procure, and cost based on project, contract, or Seiban numbers.

If Oracle Projects is installed and the Project References Enabled and Project Control Level parameters are set in the Organization Parameters window in Inventory module, you can assign project and, if required, task references to planned orders, jobs, purchase orders, sales orders, miscellaneous transaction and other entities within Oracle Manufacturing. If the Project Cost Collection Enabled parameter is also set in inventory organization parameters, you can optionally collect and transfer manufacturing cost to Oracle Projects module. Project costs are tracked by project/task and expenditure type.

/by
, , , ,

Oracle Manufacturing Strategies

CONFIGURE TO ORDER (CTO):
It is a method of manufacturing which allows you, or your customer, to choose a base product at the very moment of ordering and then configure all the variable parameters (features) associated with that product from defined/available options. Based on these selections, configurable items on each quote or order typically generates the unique product configuration and manufacturing routing and/or bill of materials based on various features and options. Vendor/order receiving company subsequently builds that configuration dynamically upon receipt of the order. The ability of the vendor to make and deliver products customized to specific customer needs offers a powerful competitive edge over competitors.

CTO is an environment in which the product or service is assembled or kitted on receipt of the sales order. Oracle EBS supports the Configure to Order environment with a range of features in order entry, demand forecasting, master scheduling, production, shipping, and financial accounting. Configure to Order includes Pick-to-Order (PTO) and Assemble-to-Order (ATO) items, models, and hybrids. It supports building configurations using other configurations as sub-assemblies (multi-level configure-to-order), internal and external sourcing of ATO models at any level in the BOM and supports multi-level PTO/ATO hybrids.
PICK TO ORDER (PTO):
It is a configure-to-order environment where the options and included items in a PTO model (finished good) appear on pick slips after you receive the sales order from customer. Pickers gather the options (based on selection rules), the predefined shippable products parts/components or service from their predefined locations using pick slip and then ship the order. It is assumed that options and components quantity are readily available. It is an alternative to manufacturing the parent item on a work order and then shipping it. There is no additional value added after getting the customer order

Example: Computer System (CPU, Monitor and Printer) A pick to order model can have PTO option class, PTO items, ATO model, ATO Option class and ATO option items. There can not be any PTO model, PTO option class or PTO item under an ATO model. You want to manufacture a promotional laptop computer, you need laptop computer, dikettes, accessories and battery pack. Here, you define PL computer as PTO model, laptop computer as ATO model, battery pack, diskette and accessories as purchase items

ASSEMBLE-TO-ORDER (ATO):

ATO simplifies the process of manufacturing finished goods. These goods are standard products and are often configured by customers from Bills of material, where you can define available options for unique product configurations. Based on forecasting, subassemblies are manufactured prior to receiving the customer order and when the order is received, the stocked subassemblies and components are assembled to make the finished products. It is an environment where you open a final assembly order to assemble items that customers orders. It is manufacturing method/strategy which allows a product to be made or service to be available to meet the needs of a specific customer order (i.e. If i am a customer i can build my own configuration from the available options). While producing finished goods on a large scale, this requires sophisticated planning processes which master schedules ATO models and options and then create work orders to build the unique configuration in WIP module while maintaining control of inventory, planning, cost accounting and Bills of Material. Planning process also anticipates changing demand for external or internal components or accessories and at the same time focuses on product customizations for individual customers

WIP, Order Management and Shipping modules support building and shipping of ATO configurations. A discrete job is created from a configuration. An assemble to order item/assembly then can be linked to a sales order. Assemble-to-order is also an item attribute in Inventory module that you can apply to standard, model, and option class items. In Bills of Material module, a model bill can be either assemble-to-order or pick-to-order and an option class bill can be either assemble-to-order or pick-to-order

Example: Automobiles, computer manufacturing…

MAKE TO STOCK (MTS):

In MTS, stock is created by companies for items without receiving an order from customer. Examples are manufacturing of refrigerators, washing nachines and Television sets. They are manufactured in a shop floor based on master schedule and stocked in finished goods subinventory until they are shipped to a cutomer

Example: You can Purchase certian goods from available vendors and manufacture some of the goods on your shop floor. and finally build a product and store and ship to your customer.

MAKE-TO-ORDER (MTO):
MTO are manufactured after receiving customer order, which means customer is willing to accept longer delivery period. The examples are commercial dish washers and refrigerators for hotels. These items are produced in a shop floor or in job shop depending up on the range of product families produced by the factory. In order to reduce lead time the factory often uses ready components to manufacture a product.

ENGINEER-TO-ORDER (ETO):

ETO item is built on the customer product specifications such as large commercial aircrafts. Such product can not be produces according to existing specifications of the company because some engineering skill is required to incorporate customer specifications in to the design of the final product. Companies using this manufacturing strategy, always quote longer lead time .The engineering and manufacturing costs involved are also high and are tracked for each order separately.

/by
, , , , , , , , ,

API To Cancel or Finally Close Requisition

Do we have any API to cancel or finally close a Purchase Requisition:

Yes, we do have one API & that is po_reqs_control_sv.update_reqs_status which can be used for finally closing or cancelling the requisition. We need to pass the parameters like requisition_header_id, requisition_line_id, Preparer_id, document_type_code,
type_lookup_code, req_control_action, Req_control_reason and the other default parameter to the API.

— R12 – PO – Sample Script to Cancel PR Using API

DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; — Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CANCEL is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prl.line_location_id
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = ‘REQUISITION’
AND prh.authorization_status = ‘APPROVED’
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = ‘21170000909’; — Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, l_org_id);

FOR i IN C_REQ_CANCEL

LOOP

dbms_output.put_line (‘ Calling po_reqs_control_sv.update_reqs_status to cancel the Requisition=>’ i.requisition_num);
dbms_output.put_line (‘======================================================’);

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => ‘CANCEL’
, X_req_control_reason => ‘CANCELLED BY API’
, X_req_action_date => SYSDATE
, X_encumbrance_flag => ‘N’
, X_oe_installed_flag => ‘Y’
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( ‘Status Found:=> ‘ X_req_control_error_rc);
DBMS_OUTPUT.PUT_LINE (‘Requisition Number cancelled is :=>’ i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Count is :=>’ cnt);

END;

— R12 – PO – Script to Finally Close PR Using API.sql

DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; — Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CLOSE is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prh.closed_code
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = ‘REQUISITION’
AND prh.authorization_status = ‘APPROVED’
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = ‘21170002264’; — Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, l_org_id);

FOR i IN C_REQ_CLOSE

LOOP

DBMS_OUTPUT.PUT_LINE (‘Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>’ i.requisition_num);

DBMS_OUTPUT.PUT_LINE (‘=======================================================’);

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => ‘FINALLY CLOSE’
, X_req_control_reason => ‘FINALLY CLOSED BY API’
, X_req_action_date => SYSDATE
, X_encumbrance_flag => ‘N’
, X_oe_installed_flag => ‘Y’
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( ‘Status Found: ‘ X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE (‘Requisition Number which is Finally Closed =>’ i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Count is :=>’ cnt);

END;

/by
, , , , , , ,

Script to Get Concurrent Request Details

select
request_id, parent_request_id,
fcpt.user_concurrent_program_name Request_Name,
fcpt.user_concurrent_program_name program_name,
DECODE(fcr.phase_code,’C’,’Completed’,’I’, ‘Incactive’,’P’,’Pending’,’R’,’Running’) phase,
DECODE(fcr.status_code, ‘D’,’Cancelled’,’U’,’Disabled’,’E’,’Error’,’M’,’No Manager’,’R’,’Normal’,’I’, ‘Normal’,
‘C’,’Normal’,’H’,’On Hold’,’W’,’Paused’,’B’,’Resuming’,’P’,’Scheduled’,’Q’,’Standby’,’S’,
‘Suspended’,’X’,’Terminated’,’T’,’Terminating’,’A’,’Waiting’,’Z’,’Waiting’,’G’,’Warning’,’N/A’) status,
round((fcr.actual_completion_date – fcr.actual_start_date),3) * 1440 as Run_Time,
round(avg(round(to_number(actual_start_date – fcr.requested_start_date),3) * 1440),2) wait_time,
fu.User_Name Requestor,
fcr.argument_text parameters,
to_char (fcr.requested_start_date, ‘MM/DD HH24:mi:SS’) requested_start,
to_char(actual_start_date, ‘MM/DD/YY HH24:mi:SS’) ACT_START,
to_char(actual_completion_date, ‘MM/DD/YY HH24:mi:SS’) ACT_COMP,
fcr.completion_text
From
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
Where 1=1
— and fu.user_name = ‘JMOHANTY’
— and fcr.request_id = 1565261
— and fcpt.user_concurrent_program_name = ‘Autoinvoice Import Program’
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcpt.application_id
and fcr.requested_by = fu.user_id
and fcpt.language = ‘US’
and fcr.actual_start_date like sysdate
— and fcr.phase_code = ‘C’
— and hold_flag = ‘Y’
— and fcr.status_code = ‘C’
GROUP BY
request_id, parent_request_id, fcpt.user_concurrent_program_name,
fcr.requested_start_date, fu.User_Name, fcr.argument_text,
fcr.actual_completion_date, fcr.actual_start_date,
fcr.phase_code, fcr.status_code,fcr.resubmit_interval,fcr.completion_text,
fcr.resubmit_interval,fcr.resubmit_interval_unit_code,fcr.description
Order by 1 desc
/by