Alert Query for Receipt Intimation - Material received at Warehouse in 11i

SELECT   hre.full_name

,        SUBSTR(poh.segment1,1,10) || '/' ||

               SUBSTR(pol.line_num,1,4) || '/' ||

               SUBSTR(por.release_num,1,4) || '/' ||

               SUBSTR(pll.shipment_num,1,4)

,        pov.vendor_name

,        pol.item_description

INTO     &AGENT

,        &PO

,        &VENDOR

,        &LINE

FROM     hr_employees_current_v hre

,        po_vendors pov

,        po_headers poh

,        po_lines pol

,        po_line_locations pll

,        po_releases por

WHERE    poh.type_lookup_code        IN ('STANDARD','BLANKET','PLANNED')

AND      NVL(pll.receipt_required_flag, 'N') = 'N'

AND      NVL(pll.closed_code, 'OPEN') = 'OPEN'

AND      NVL(pll.cancel_flag, 'N')    = 'N'

AND      NVL(poh.closed_code, 'OPEN') = 'OPEN'

AND      NVL(poh.cancel_flag, 'N')    = 'N'

AND      NVL(por.closed_code, 'OPEN') = 'OPEN'

AND      NVL(por.cancel_flag, 'N')    = 'N'

AND      poh.po_header_id             = pol.po_header_id

AND      pov.vendor_id                = poh.vendor_id

AND      pll.po_line_id               = pol.po_line_id

AND      por.po_release_id(+)         = pll.po_release_id

AND      hre.employee_id              = poh.agent_id

AND      pol.creation_date            BETWEEN (sysdate - :CHECKDAYS)

                                              AND sysdate

ORDER BY hre.full_name

,        poh.segment1

,        pov.vendor_name

,        pol.creation_date

select rowidtochar(cust.rowid),

substr(hr.name,1,3) "ORGN_CODE",

a.receipt_num,

sysdate,

b.quantity_received,

b.line_num,

e.segment1,

c.segment1,

c.Description,

substr(k.email_address,1,55),

substr(l.email_address,1,55),

substr(m.email_address,1,55),

decode(e.org_id,'46','abc@rrr.com','42','hod@rrr.com',NULL),

nvl(j.segment1,'NULL'),

l.user_name

into &rowid,&orgn,&recvno,&recv_date,&recv_qty,

&lineno,&pono,&itemno,&item_desc1,&email1,&email2,&email3,&email4,&reqno,&username

from PO_RECEIPT_ALERT_CUSTOM cust,

rcv_shipment_headers a,

rcv_shipment_lines b,

mtl_system_items c,

ic_item_mst d,

po_headers_all e,

po_requisition_headers_all j,

po_requisition_lines_all porl,

po_line_locations_all poll,

fnd_user k,fnd_user l,fnd_user m,

hr_organization_units hr

where cust.shipment_header_id = a.shipment_header_id

and a.shipment_header_id = b.shipment_header_id

and d.item_no = c.segment1

and d.gl_class in ('GS','RM','FA','PY')

and b.po_header_id = e.po_header_id

and a.ship_to_org_id = hr.organization_id

and b.item_id = c.inventory_item_id

and a.ship_to_org_id = c.organization_id

and b.po_line_location_id = poll.line_location_id

and poll.line_location_id = porl.line_location_id(+)

and j.requisition_header_id(+) = porl.requisition_header_id

and e.created_by = k.user_id

and decode(j.created_by,null,e.created_by,j.created_by) = l.user_id

and a.created_by = m.user_id

and cust.rowid =:rowid

order by b.line_num

select rowidtochar(cust.rowid),

:mailid,

substr(hr.name,1,3) "ORGN_CODE",

a.receipt_num,

--a.recv_date,

sysdate,

b.quantity_received,

b.line_num,

e.segment1,

c.segment1,

c.Description,

substr(k.email_address,1,55),

substr(l.email_address,1,55),

substr(m.email_address,1,55),

nvl(j.segment1,'NULL'),

l.user_name

into &rowid,&mailid,&orgn,&recvno,&recv_date,&recv_qty,&lineno,&pono,&itemno,&item_desc1,&email1,&email2,&email3,&reqno,&username

from PO_RECEIPT_ALERT_CUSTOM cust,

rcv_shipment_headers a,

rcv_shipment_lines b,

mtl_system_items c,

ic_item_mst d,

po_headers_all e,

po_requisition_headers_all j,

po_requisition_lines_all porl,

po_line_locations_all poll,

fnd_user k,fnd_user l,fnd_user m,

hr_organization_units hr

where cust.shipment_header_id = a.shipment_header_id

and a.shipment_header_id = b.shipment_header_id

and d.item_no = c.segment1

and d.gl_class in ('GS','RM','FA','PY')

and b.po_header_id = e.po_header_id

and a.ship_to_org_id = hr.organization_id

and b.item_id = c.inventory_item_id

and a.ship_to_org_id = c.organization_id

and b.po_line_location_id = poll.line_location_id

and poll.line_location_id = porl.line_location_id(+)

and j.requisition_header_id(+) = porl.requisition_header_id

and e.created_by = k.user_id

and decode(j.created_by,null,e.created_by,j.created_by) = l.user_id

and a.created_by = m.user_id

and cust.rowid =:rowid

order by b.line_num



Re-open the finally closed po in Oracle R12 /11i

Query for re-open the finally closed PO in Oracle R12/11i

update     po_headers_all

set     closed_code = 'OPEN',

     authorization_status = 'REQUIRES REAPPROVAL'

     approved_flag = 'R',

     wf_item_type  = NULL,

     wf_item_key   = NULL

where     po_header_id  = 62170;



update     po_action_history

set     action_code = 'NO ACTION',

     action_date = trunc(sysdate),

     note        = 'updated by reset script on '||to_char(trunc(sysdate))

where     object_id   = 62170 --po_header_id

and     object_type_code = 'PO'

and     object_sub_type_code in ('STANDARD','PLANNED');



Best Thoughts

If you can't fly, then run,
If you can't run, then walk,
If you can't walk, then crawl,
but whatever you do,
you have to keep moving forward.

  - Martin Luther King Jr.

When you are going through hell, keep going
Don't let a bad day make you feel like you have a bad life.


It doesn't matter what people think about you. It's what you think of yourself that really counts.



Before you act, Listen
Before you react, think
Before you spend, Earn
Before you Criticize, wait
Before you pray, forgive
BEFORE YOU QUIT, TRY

Failed to import the order line because Create_Delivery_Details failed

Registration order, the following error
1.Failed to import the order line because Create_Delivery_Details failed. 1.Failed to import the order line because Create_Delivery_Details failed.
2. Workflow activity Ship has failed. Please fix the errors and retry the activity from the Order Form or the Workflow Monitor. 2. Workflow activity Ship has failed. Please fix the errors and retry the activity from the Order Form or the Workflow Monitor.
3.Order has been booked. 3.Order has been booked.


Later in METALINK found on solutions, the need to fight a patch number is: 688704, patch, normal registered

PS: In the pick library, did not play this patch before picking warehouse option can not be displayed correctly appear natural warehouses, kick patches can also show normal

From METALINK :

On 11.5.10.2 in Production: On 11.5.10.2 in Production:
When attempting to book a new sales order, the following error occurs. When attempting to book a new sales order, the following error occurs.

ERROR ERROR
Error: Failed to import the order line because Create_Delivery_Details failed. Error: Failed to import the order line because Create_Delivery_Details failed.
Workflow activity Ship has failed. Please fix the errors and retry the activity from the Order Form or the Workflow Monitor. Workflow activity Ship has failed. Please fix the errors and retry the activity from the Order Form or the Workflow Monitor.
Order has been booked. Order has been booked.

Cause Cause

The Import Shipping location is not importing the locations from the HR/HZ location tables having last_update_date as NULL. In the procedure Process_locations, the cursors selecting the eligible locations from HZ/HR locations is not handling the NULL last_update_date records. The Import Shipping location is not importing the locations from the HR / HZ location tables having last_update_date as NULL. In the procedure Process_locations, the cursors selecting the eligible locations from HZ / HR locations is not handling the NULL last_update_date records.

This issue has been fixed in the file WSHLOCMB.pls in version 115.36.11510.7. This issue has been fixed in the file WSHLOCMB.pls in version 115.36.11510.7.

This is explained in the following bug: This is explained in the following bug:
Bug 4688704 : NO LOCATION SETUP FOR THE ORGANIZATION ERROR WHILE BOOKING ORDER Bug 4688704: NO LOCATION SETUP FOR THE ORGANIZATION ERROR WHILE BOOKING ORDER

Solution Solution

To implement the solution, please execute the following steps: To implement the solution, please execute the following steps:

1. Please download and review the readme and pre-requisites for Patch 4688704. 1. Please download and review the readme and pre-requisites for Patch 4688704.

2. Please ensure that you have taken a backup of your system before applying the recommended patch. 2. Please ensure that you have taken a backup of your system before applying the recommended patch.

3. Please apply the patch in a test environment. 3. Please apply the patch in a test environment.

4. Please confirm the following file versions: 4. Please confirm the following file versions:

WSHLOCMB.pls 115.36.11510.7 WSHLOCMB.pls 115.36.11510.7

5. Please retest the issue. 5. Please retest the issue.

6. If the issue is resolved, please migrate the solution as appropriate to other environments. 6. If the issue is resolved, please migrate the solution as appropriate to other environments.

Re-open a Final Closed OPM Inventory Period in 11i

To Re-open a Final Closed OPM Inventory Period in 11i?

Please Note:

1.    You can use these scripts when rest of the Process like Final Cost Update, Final Sub-ledger Update, Transfer and Post to GL is not done.

2.    Test it on the test environment and then apply to Production

The following script will reverse the final closed OPM Inventory period to Preliminary close status.

update ic_cldr_dtl
set closed_period_ind = 2
where orgn_code = upper('&&orgn_code')
and fiscal_year = &&fiscal_year
and period = &&period

Provide the following parameters,

Orgn_code -- provide the Operating Unit

Fiscal_year -- give the fiscal year

Period -- the period for which the status is to be changed

Confirm that only 1 row gets updated.

update ic_whse_sts
set close_whse_ind = 2
where fiscal_year = &&fiscal_year
and period = &&period
and whse_code in ('XXX','YYY','ZZZ')

Parameters:

Fiscal_year -- give the same fiscal year
Period -- give the same period
Whse_code -- give all the warehouses of the Operating unit

“N” number of rows will get updated – depending upon the No. of warehouses in the corresponding Operating Unit.

Click Commit button.

If we change the value of “set closed_period_ind = 2” in the 1st query & “set close_whse_ind = 2” in the 2nd query to “1” and execute the queries in the same order, the final closed OPM Inventory period will be reversed to Open status.

How to Re-Open a Closed Inventory Accounting Period

Applies to:

Oracle Cost Management - Version: 11.5.10.0 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
Scripts to Re-open an Inventory Accounting Period that has accidentally been closed.

Goal

If an Inventory Accounting Period has been closed prematurely by accident the following scripts can be used to re-open the accounting period if the corresponding GL period is open.  Re-Opening a closed period will allow transactions to be process for that period.

The re-opening of a closed period should not be used to back date transactions, the system allow back dated transactions but this may cause discrepancies between inventory and GL. Any discrepancies caused by back dated transactions are not supported by Oracle and would have to be resolved with a manual adjustment to the General Ledger.

Solution

DISCLAIMER: THE RE-OPENING OF A CLOSED INVENTORY PERIOD COULD POTENTIALLY CAUSE DATA CORRUPTION AND ANY DATA CORRUPTION CAUSED BY RE-OPENING A CLOSED INVENTORY PERIOD WILL BE THE RESPONSIBILITY OF THE CUSTOMER AND NO DATA FIX WILL BE PROVIDED FOR ANY DATA CORRUPTION THAT HAS BEEN CAUSED BY RE-OPENING A CLOSED PERIOD.

TEST THOROUGHLY ALL SCRIPTS ON A NON-PRODUCTION INSTANCE, FIRST BACKING UP ALL TABLE DATA PRIOR TO IMPLEMENTING IN PRODUCTION.

IF THERE IS CONCERN THAT RE-OPENING A CLOSED PERIOD MAY CAUSE DATA CORRUPTION PLEASE OPEN AN SR WITH ORACLE SUPPORT PRIOR TO RE-OPENING A CLOSED PERIOD.

-- A script to list all inventory periods for a specific organization
-- A script to reopen closed inventory accounting periods in 11.5.10
-- The script will reopen all inventory periods for the specified
-- Delete scripts to remove the rows created during the period close process to prevent duplicate rows
-- organization starting from the specified accounting period.
-- The organization_id can be obtained from the MTL_PARAMETERS table.
-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table. 

   

   

   

SELECT acct_period_id period, open_flag, period_name name,        period_start_date, schedule_close_date, period_close_date        FROM  org_acct_periods        WHERE organization_id = &org_id        order by 1,2;

UPDATE org_acct_periods
SET open_flag = 'Y',
period_close_date = NULL,
summarized_flag = 'N'
WHERE organization_id = &&org_id
AND acct_period_id> = &&acct_period_id;

DELETE mtl_period_summary
WHERE organization_id = &org_id
AND acct_period_id> = &acct_period_id;

DELETE mtl_period_cg_summary
WHERE organization_id = &org_id
AND acct_period_id> = &acct_period_id;

DELETE mtl_per_close_dtls
WHERE organization_id = &org_id
AND acct_period_id> = &acct_period_id;

DELETE cst_period_close_summary
WHERE organization_id = &org_id
AND acct_period_id> = &acct_period_id; 

commit

If Inspection in different month want to correct it. You can do it in R12

If Inspection in different month want to correct it.  You can do it in R12

update rcv_transactions

1. set transaction_date='30-MAR-2011'
   where transaction_id='10595'
   
   

When PO is finally closed and you want to re-open in Oracle EBS 11i or R12

When PO is finally closed and you want to re-open in Oracle EBS 11i or R12

Change po_header_id,  po_line_id, Org_id as per your database and PO

update po_line_locations_all

set closed_code='',

    closed_date='',

    CLOSED_FLAG='N'

where po_header_id= 173172

and   po_line_id= 441159

and   org_id= 46;

update po_lines_all

set closed_code='',

    closed_date='',

    CLOSED_FLAG='N'

where po_header_id=173172

and   po_line_id= 441159

and   org_id= 46;

update po_headers_all

set closed_code='',

    closed_date=''   

where po_header_id = 173172  

and   org_id= 46;