[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT usr.user_id
INTO p_user_id
FROM fnd_user usr,
ecx_doclogs ecx
WHERE usr.user_name = upper(ecx.username)
AND ecx.internal_control_number = p_int_control_number;
select 1
into lv_admin
from fnd_user_resp_groups furg,
fnd_responsibility_vl frv
where furg.USER_ID = pUSER_ID
and frv.RESPONSIBILITY_NAME like '%Supply Chain Collaboration Administrator%'
and frv.APPLICATION_ID = 724
and frv.APPLICATION_ID = furg.RESPONSIBILITY_APPLICATION_ID
and frv.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
and trunc(nvl(furg.end_date,sysdate)) >= trunc(sysdate)
and rownum = 1;
PROCEDURE update_errors (
p_header_id IN NUMBER,
p_language IN VARCHAR2,
p_build_err IN NUMBER,
p_date_format IN VARCHAR2
, p_consumption_advice_exists OUT NOCOPY BOOLEAN -- bug 3551850
) IS
l_err_msg VARCHAR2(2000);
SELECT l.line_id
FROM msc_supdem_lines_interface l
WHERE l.parent_header_id = p_header_id
AND l.line_id BETWEEN p_start_line AND p_end_line
AND NVL(l.row_status, G_PROCESS) = G_PROCESS
AND checkdates(p_header_id,l.line_id,p_date_format) = 1;
select line_id,
nvl(item_name,
nvl(owner_item_name,
nvl(customer_item_name,
supplier_item_name))),
substrb(order_type,1,80) ,
err_msg
from msc_supdem_lines_interface
where parent_header_id = p_header_id and
line_id between p_start_line and p_end_line and
row_status = 4;
select distinct upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id,
ln.order_identifier,
ln.release_number,
ln.line_number,
ln.pegging_order_identifier,
ln.ref_release_number,
ln.ref_line_number
from msc_supdem_lines_interface ln,
fnd_lookup_values flv
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_PURCHASE_ORDER,
G_SALES_ORDER,
G_ASN,
G_SHIP_RECEIPT,
G_REQUISITION,
G_PO_ACKNOWLEDGEMENT)
and upper(ln.sync_indicator) = 'R';
select distinct upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id
from msc_supdem_lines_interface ln,
fnd_lookup_values flv
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_ALLOC_ONHAND, g_unallocated_onhand)
and upper(ln.sync_indicator) = 'R';
SELECT DISTINCT Upper(ln.publisher_company),
Upper(ln.publisher_site),
Upper(ln.customer_company),
Upper(ln.customer_site),
Upper(ln.order_type),
ln.inventory_item_id,
ln.order_identifier
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv
WHERE ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code = g_work_order
and upper(ln.sync_indicator) = 'R';
select upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id,
trunc(to_date(ln.key_date,p_date_format)),
upper(ln.bucket_type)
from msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
g_proj_avai_bal)
and flv1.language = flv.language
and flv1.lookup_type = 'MSC_X_BUCKET_TYPE'
and flv1.lookup_code = G_DAY
and upper(flv1.meaning) = upper(ln.bucket_type)
and upper(ln.sync_indicator) = 'R'
union
select upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id,
trunc(to_date(ln.key_date,p_date_format)),
upper(ln.bucket_type)
from msc_supdem_lines_interface ln,
fnd_lookup_values flv
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
g_proj_avai_bal)
and ln.bucket_type is null
and upper(ln.sync_indicator) = 'R';
select upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id,
trunc(to_date(ln.key_date,p_date_format)),
upper(ln.bucket_type)
from msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
g_proj_avai_bal)
and flv1.language = flv.language
and flv1.lookup_type = 'MSC_X_BUCKET_TYPE'
and flv1.lookup_code = G_WEEK
and upper(flv1.meaning) = upper(ln.bucket_type)
and upper(ln.sync_indicator) = 'R';
select upper(ln.publisher_company),
upper(ln.publisher_site),
upper(ln.customer_company),
upper(ln.customer_site),
upper(ln.supplier_company),
upper(ln.supplier_site),
upper(ln.order_type),
ln.inventory_item_id,
trunc(to_date(ln.key_date,p_date_format)),
upper(ln.bucket_type)
from msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1
where ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code in (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
g_proj_avai_bal)
and flv1.language = flv.language
and flv1.lookup_type = 'MSC_X_BUCKET_TYPE'
and flv1.lookup_code = G_MONTH
and upper(flv1.meaning) = upper(ln.bucket_type)
and upper(ln.sync_indicator) = 'R';
SELECT ln.line_id, flv.lookup_code
FROM msc_supdem_lines_interface ln
, fnd_lookup_values flv
WHERE ln.parent_header_id = p_header_id
AND ln.line_id BETWEEN p_start_line AND p_end_line
AND Nvl(ln.row_status, g_process) = g_process
AND flv.lookup_type = 'MSC_X_ORDER_TYPE'
AND flv.language = p_language
AND Upper(flv.meaning) = Upper(ln.order_type);
CURSOR c_delete_supply_commit (
p_header_id IN NUMBER,
p_language IN VARCHAR2
) IS
SELECT DISTINCT c.company_id,
cs.company_site_id,
c1.company_id,
cs1.company_site_id,
ln.inventory_item_id
FROM msc_companies c,
msc_company_sites cs,
msc_companies c1,
msc_company_sites cs1,
fnd_lookup_values flv,
msc_supdem_lines_interface ln
WHERE ln.parent_header_id = p_header_id
AND ln.row_status = G_SUCCESS
AND Upper(c.company_name) = Upper(ln.publisher_company)
AND c.company_id = cs.company_id
AND Upper(cs.company_site_name) = Upper(ln.publisher_site)
AND Upper(c1.company_name) = Upper(ln.customer_company)
AND c1.company_id = cs1.company_id
AND Upper(cs1.company_site_name) = Upper(ln.customer_site)
AND flv.lookup_type = 'MSC_X_ORDER_TYPE'
AND Upper(flv.meaning) = Upper(ln.order_type)
AND flv.language = p_language
AND flv.lookup_code = g_supply_commit;
CURSOR c_delete_order_forecast (
p_header_id IN NUMBER,
p_language IN VARCHAR2
) IS
SELECT DISTINCT c.company_id,
cs.company_site_id,
c1.company_id,
cs1.company_site_id,
ln.inventory_item_id
FROM msc_companies c,
msc_company_sites cs,
msc_companies c1,
msc_company_sites cs1,
fnd_lookup_values flv,
msc_supdem_lines_interface ln
WHERE ln.parent_header_id = p_header_id
AND ln.row_status = G_SUCCESS
AND Upper(c.company_name) = Upper(ln.publisher_company)
AND c.company_id = cs.company_id
AND Upper(cs.company_site_name) = Upper(ln.publisher_site)
AND Upper(c1.company_name) = Upper(ln.supplier_company)
AND c1.company_id = cs1.company_id
AND Upper(cs1.company_site_name) = Upper(ln.supplier_site)
AND flv.lookup_type = 'MSC_X_ORDER_TYPE'
AND Upper(flv.meaning) = Upper(ln.order_type)
AND flv.language = p_language
AND flv.lookup_code = G_ORDER_FORECAST;
select 1 into calendar_is_not_seeded
from dual
where exists ( select 'exists'
from msc_calendar_dates
where calendar_code = 'CP-Mon-70'
and exception_set_id = -1
and sr_instance_id = 0 );
select min(line_id) into l_min
from msc_supdem_lines_interface
where parent_header_id = p_header_id;
update msc_supdem_lines_interface
set row_status = 4,
err_msg = substrb(l_err_msg,1,1000)
where parent_header_id = p_header_id
and line_id = l_min;
update msc_supdem_lines_interface
set row_status = 1
where parent_header_id = p_header_id
and line_id <> l_min;
SELECT min(line_id), max(line_id)
INTO l_min, l_max
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id;
SELECT line_id
BULK COLLECT INTO t_line_id
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id AND
line_id BETWEEN l_start_line and l_end_line;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT l.lookup_code
FROM fnd_lookup_values l
WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(l.meaning) = NVL(UPPER(ln.order_type), G_NULL_STRING) AND
l.language = p_language);
SELECT flv.lookup_code,
ln.line_id
BULK COLLECT into t_order_type, t_line_id
FROM fnd_lookup_values flv,
msc_supdem_lines_interface ln
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
Upper(flv.meaning(+)) = Upper(ln.order_type) and
nvl(ln.row_status, G_PROCESS) = G_PROCESS and
ln.parent_header_id = p_header_id and
ln.line_id between l_start_line and l_end_line;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.posting_party_name, G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(ln.sync_indicator, G_NULL_STRING)) NOT IN ('R','D');
update msc_supdem_lines_interface ln
set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
where ln.parent_header_id = p_header_id and
ln.line_id = t_line_id(j) and
NVL(ln.row_status, G_PROCESS) = G_PROCESS and
ln.item_name is NULL and
ln.owner_item_name is NULL and
ln.supplier_item_name is NULL and
ln.customer_item_name is NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
t_order_type(j) is NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.order_identifier is NULL AND
t_order_type(j) = G_CONS_ADVICE ;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.publisher_company));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company <> NVL(DECODE(t_order_type(j),G_REQUISITION,ln.customer_company,
G_ORDER_FORECAST,ln.customer_company,
G_HIST_SALES,ln.posting_party_name,
G_SELL_THRO_FCST,ln.posting_party_name,
G_ALLOC_ONHAND,ln.customer_company,
G_PURCHASE_ORDER,ln.customer_company,
G_SHIP_RECEIPT,ln.customer_company,
G_SUPPLY_COMMIT,ln.supplier_company,
G_SUPPLIER_CAP,ln.supplier_company,
G_SAFETY_STOCK,ln.supplier_company,
G_SALES_ORDER,ln.supplier_company,
G_ASN,ln.supplier_company,
G_WORK_ORDER,ln.supplier_company,
G_REPLENISHMENT,ln.supplier_company,
G_PO_ACKNOWLEDGEMENT,ln.supplier_company,
G_SALES_FORECAST,ln.posting_party_name,
G_UNALLOCATED_ONHAND,nvl(customer_company, supplier_company),
G_CONS_ADVICE, nvl(customer_company, supplier_company)
),publisher_company);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_site <> NVL(DECODE(t_order_type(j),G_REQUISITION,ln.customer_site,
G_ORDER_FORECAST,ln.customer_site,
G_HIST_SALES, decode(posting_party_name,customer_company,customer_site,supplier_company,supplier_site,null),
G_SELL_THRO_FCST,decode(posting_party_name,customer_company,customer_site,supplier_company,supplier_site,null),
G_ALLOC_ONHAND,ln.customer_site,
G_PURCHASE_ORDER,ln.customer_site,
G_SHIP_RECEIPT,ln.customer_site,
G_SUPPLY_COMMIT,ln.supplier_site,
G_SUPPLIER_CAP,ln.supplier_site,
G_SAFETY_STOCK,ln.supplier_site,
G_SALES_ORDER,ln.supplier_site,
G_ASN,ln.supplier_site,
G_WORK_ORDER,ln.supplier_site,
G_REPLENISHMENT,ln.supplier_site,
G_PO_ACKNOWLEDGEMENT,ln.supplier_site,
G_SALES_FORECAST, decode(posting_party_name,customer_company,customer_site,supplier_company,supplier_site,null),
G_UNALLOCATED_ONHAND,nvl(customer_site, supplier_site),
G_CONS_ADVICE, nvl(customer_site, supplier_site)
),publisher_site);
UPDATE msc_supdem_lines_interface ln
SET ln.publisher_company = NVL(decode(t_order_type(j),
G_SALES_FORECAST, ln.posting_party_name,
G_ORDER_FORECAST, ln.customer_company,
G_SUPPLY_COMMIT, ln.supplier_company,
G_HIST_SALES, ln.posting_party_name,
G_SELL_THRO_FCST, ln.posting_party_name,
G_SUPPLIER_CAP, ln.supplier_company,
G_SAFETY_STOCK, ln.supplier_company,
G_ALLOC_ONHAND, ln.customer_company,
G_UNALLOCATED_ONHAND,
nvl(ln.customer_company, ln.supplier_company),
G_PURCHASE_ORDER, ln.customer_company,
G_SALES_ORDER, ln.supplier_company,
G_ASN, ln.supplier_company,
G_SHIP_RECEIPT, ln.customer_company,
G_REPLENISHMENT, ln.supplier_company,
G_REQUISITION, ln.customer_company,
G_PO_ACKNOWLEDGEMENT, ln.supplier_company,
G_WORK_ORDER, ln.supplier_company),ln.publisher_company),
ln.publisher_site = NVL(decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_ORDER_FORECAST, ln.customer_site,
G_SUPPLY_COMMIT, ln.supplier_site,
G_HIST_SALES,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_SUPPLIER_CAP, ln.supplier_site,
G_SAFETY_STOCK, ln.supplier_site,
G_ALLOC_ONHAND, ln.customer_site,
G_UNALLOCATED_ONHAND,
nvl(ln.customer_site, ln.supplier_site),
G_PURCHASE_ORDER, ln.customer_site,
G_SALES_ORDER, ln.supplier_site,
G_ASN, ln.supplier_site,
G_SHIP_RECEIPT, ln.customer_site,
G_REPLENISHMENT, ln.supplier_site,
G_REQUISITION, ln.customer_site,
G_PO_ACKNOWLEDGEMENT, ln.supplier_site,
G_WORK_ORDER, ln.supplier_site,
G_CONS_ADVICE, nvl(ln.customer_site,
ln.supplier_site)),
ln.publisher_site)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company IS NOT NULL AND
ln.publisher_site IS NOT NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
ln.row_status = G_FAILURE
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
not exists ( select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.supplier_company,G_NULL_STRING))
union select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.customer_company,G_NULL_STRING))
union select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.publisher_company,G_NULL_STRING))
);
UPDATE msc_supdem_lines_interface ln
SET ln.customer_company = DECODE(t_order_type(j),
G_SALES_FORECAST,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_ORDER_FORECAST, null,
G_HIST_SALES,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_SELL_THRO_FCST,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_ALLOC_ONHAND, null,
G_UNALLOCATED_ONHAND, null,
G_PURCHASE_ORDER, null,
G_SHIP_RECEIPT, null,
G_REQUISITION, null,
G_SAFETY_STOCK, null,
g_proj_avai_bal, NULL,
ln.customer_company),
ln.customer_site = DECODE(t_order_type(j),
G_SALES_FORECAST,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_ORDER_FORECAST, null,
G_HIST_SALES,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_SELL_THRO_FCST,
DECODE(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_ALLOC_ONHAND, null,
G_UNALLOCATED_ONHAND, null,
G_CONS_ADVICE, null,
G_PURCHASE_ORDER, null,
G_SHIP_RECEIPT, null,
G_REQUISITION, null,
G_SAFETY_STOCK, null,
g_proj_avai_bal, NULL,
ln.customer_site),
ln.supplier_company = DECODE(t_order_type(j),
G_SALES_FORECAST,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_HIST_SALES,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_SELL_THRO_FCST,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_SUPPLY_COMMIT, null,
G_SUPPLIER_CAP, null,
G_SAFETY_STOCK, null,
g_proj_avai_bal, NULL,
G_UNALLOCATED_ONHAND, null,
G_CONS_ADVICE, null,
G_SALES_ORDER, null,
G_ASN, null,
G_REPLENISHMENT, null,
G_PO_ACKNOWLEDGEMENT, null,
/* Added for work order support */
G_WORK_ORDER, NULL,
ln.supplier_company),
ln.supplier_site = DECODE(t_order_type(j),
G_SALES_FORECAST,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_HIST_SALES,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_SELL_THRO_FCST,
DECODE(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_SUPPLY_COMMIT, null,
G_SUPPLIER_CAP, null,
G_SAFETY_STOCK, null,
g_proj_avai_bal, NULL,
G_UNALLOCATED_ONHAND, null,
G_CONS_ADVICE, null,
G_SALES_ORDER, null,
G_ASN, null,
G_REPLENISHMENT, null,
G_PO_ACKNOWLEDGEMENT, null,
/* Added for work order support */
G_WORK_ORDER, NULL,
G_CONS_ADVICE, NULL,
ln.supplier_site)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company IS NOT NULL AND
ln.publisher_site IS NOT NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.bucket_type IS NOT NULL AND
NOT EXISTS (SELECT l.lookup_code
FROM fnd_lookup_values l
WHERE l.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(l.meaning) = NVL(UPPER(ln.bucket_type),
G_NULL_STRING) AND
l.language = p_language);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_err_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS;
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure
WHERE ln.err_msg IS NOT NULL
AND Upper(ln.sync_indicator) = 'D'
AND ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j);
update msc_supdem_lines_interface ln
set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
where ln.parent_header_id = p_header_id and
ln.line_id = t_line_id(j) and
NVL(ln.row_status, G_PROCESS) = G_PROCESS and
ln.sync_indicator = 'D' and
not exists (select i.inventory_item_id
from msc_items i
where i.item_name = ln.item_name
UNION
select msi.inventory_item_id
from msc_system_items msi
where msi.item_name = NVL(ln.owner_item_name,
NVL(ln.customer_item_name,
ln.supplier_item_name)) and
msi.plan_id = -1
UNION
select mis.inventory_item_id
from msc_item_suppliers mis
where mis.supplier_item_name = NVL(ln.owner_item_name,
NVL(ln.customer_item_name,
ln.supplier_item_name)) and
mis.plan_id = -1
UNION
select mic.inventory_item_id
from msc_item_customers mic
where mic.customer_item_name = NVL(ln.owner_item_name,
NVL(ln.customer_item_name,
ln.supplier_item_name)) and
mic.plan_id = -1 );
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.publisher_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(ln.publisher_site));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c,
msc_companies c1,
msc_company_relationships r
WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
r.subject_id = c1.company_id and
r.object_id = c.company_id and
r.relationship_type = 1);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.customer_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.customer_site,
G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.supplier_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c,
msc_companies c1,
msc_company_relationships r
WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
r.subject_id = c1.company_id and
r.object_id = c.company_id and
r.relationship_type = 2);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.supplier_company IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.supplier_site,
G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = Decode(t_tmp_ot(j),
G_WORK_ORDER,ln.wip_end_date,
G_PURCHASE_ORDER, -- jguo
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.ship_date,
ln.receipt_date),
G_SHIP_RECEIPT, ln.receipt_date,
G_ORDER_FORECAST,-- jguo
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.ship_date,
ln.receipt_date),
G_REQUISITION, -- jguo
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.ship_date,
ln.receipt_date),
G_SELL_THRO_FCST, ln.ship_date,
G_SUPPLIER_CAP, ln.ship_date,
G_HIST_SALES, ln.new_schedule_date,
G_SAFETY_STOCK, ln.new_schedule_date,
G_ALLOC_ONHAND, ln.new_schedule_date,
G_UNALLOCATED_ONHAND, ln.new_schedule_date,
g_safety_stock, ln.new_schedule_date,
g_proj_avai_bal, ln.new_schedule_date,
G_CONS_ADVICE, ln.new_schedule_date,
G_SUPPLY_COMMIT, -- jguo Nvl(ln.ship_date, ln.receipt_date),
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.ship_date,
Nvl(ln.receipt_date, ln.ship_date)),
G_ASN, -- jguo Nvl(ln.ship_date, ln.receipt_date),
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.receipt_date,
nvl(ln.receipt_date, ln.ship_date)),
G_SALES_FORECAST, -- jguo Nvl(ln.ship_date, ln.receipt_date),
DECODE(ln.shipping_control,
1, ln.ship_date,
2, ln.ship_date,
Nvl(ln.ship_date, ln.receipt_date)),
G_SALES_ORDER, -- jguo Nvl(ln.ship_date, ln.receipt_date),
DECODE(ln.shipping_control,
1, ln.receipt_date,
2, ln.ship_date,
Nvl(ln.receipt_date, ln.ship_date)), -- jguo
NULL)
WHERE ln.parent_header_id = p_header_id
AND ln.line_id = t_tmp_line_id(j)
AND Upper(ln.sync_indicator) = 'D';
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
Upper(ln.sync_indicator) = 'D' AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.key_date IS NOT NULL AND
ln.new_schedule_end_date IS NOT NULL AND
to_date(ln.new_schedule_end_date, p_date_format) <
to_date(ln.key_date, p_date_format);
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure
WHERE ln.err_msg IS NOT NULL
AND Upper(ln.sync_indicator) = 'D'
AND ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j);
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_success
WHERE ln.err_msg IS NULL
AND Upper(ln.sync_indicator) = 'D'
AND ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j);
SELECT line_id
BULK COLLECT INTO t_line_id
FROM msc_supdem_lines_interface
WHERE sync_indicator = 'R' AND
NVL(row_status, G_PROCESS) = G_PROCESS AND
parent_header_id = p_header_id AND
line_id BETWEEN l_start_line and l_end_line;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company IS NOT NULL and
ln.supplier_company IS NOT NULL and
ln.customer_company IS NOT NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
nVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NVL(ln.quantity,-1) < 0;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.ship_from_party_name IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.ship_from_party_name));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.ship_to_party_name IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.ship_to_party_name));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.end_order_publisher_name IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.end_order_publisher_name));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.pegging_order_identifier IS NOT NULL AND
ln.end_order_type IS NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.end_order_type IS NOT NULL AND
NOT EXISTS (SELECT l.lookup_code
FROM fnd_lookup_values l
WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(l.meaning) = NVL(UPPER(ln.end_order_type), G_NULL_STRING) AND
l.language = p_language);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT u.uom_code
FROM msc_units_of_measure u
WHERE u.uom_code = NVL(ln.uom, G_UOM));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure
WHERE ln.err_msg IS NOT NULL
AND Upper(ln.sync_indicator) = 'R'
AND ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j);
SELECT flv.lookup_code,
ln.line_id
BULK COLLECT into t_order_type, t_line_id1
FROM fnd_lookup_values flv,
msc_supdem_lines_interface ln
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.meaning = ln.order_type and
nvl(ln.row_status, G_PROCESS) = G_PROCESS and
ln.parent_header_id = p_header_id and
ln.line_id between l_start_line and l_end_line;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
(ln.customer_company IS NULL OR
ln.supplier_company IS NULL) AND
t_order_type(j) NOT IN (g_unallocated_onhand, g_work_order, g_safety_stock, g_proj_avai_bal, G_CONS_ADVICE);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NULL AND
ln.supplier_company IS NULL AND
t_order_type(j) IN (g_unallocated_onhand, g_work_order, g_safety_stock, g_proj_avai_bal, G_CONS_ADVICE);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company <> ln.posting_party_name AND
ln.supplier_company <> ln.posting_party_name AND
G_SALES_FORECAST = t_order_type(j);
UPDATE msc_supdem_lines_interface ln
SET ln.publisher_company = decode(t_order_type(j),
G_SALES_FORECAST, ln.posting_party_name,
G_ORDER_FORECAST, ln.customer_company,
G_SUPPLY_COMMIT, ln.supplier_company,
G_HIST_SALES, ln.posting_party_name,
G_SELL_THRO_FCST, ln.posting_party_name,
G_SUPPLIER_CAP, ln.supplier_company,
G_ALLOC_ONHAND, ln.customer_company,
G_UNALLOCATED_ONHAND,
nvl(ln.customer_company, ln.supplier_company),
g_safety_stock,
nvl(ln.customer_company, ln.supplier_company),
g_proj_avai_bal,
nvl(ln.customer_company, ln.supplier_company),
G_CONS_ADVICE, nvl(ln.customer_company,
ln.supplier_company),
G_PURCHASE_ORDER, ln.customer_company,
G_SALES_ORDER, ln.supplier_company,
G_ASN, ln.supplier_company,
G_SHIP_RECEIPT, ln.customer_company,
G_REPLENISHMENT, ln.supplier_company,
G_REQUISITION, ln.customer_company,
G_PO_ACKNOWLEDGEMENT, ln.supplier_company,
G_WORK_ORDER, ln.supplier_company),
ln.publisher_site = decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_ORDER_FORECAST, ln.customer_site,
G_SUPPLY_COMMIT, ln.supplier_site,
G_HIST_SALES,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.customer_company,
ln.customer_site,
ln.supplier_company,
ln.supplier_site,
null),
G_SUPPLIER_CAP, ln.supplier_site,
G_ALLOC_ONHAND, ln.customer_site,
G_UNALLOCATED_ONHAND,
nvl(ln.customer_site, ln.supplier_site),
g_safety_stock,
nvl(ln.customer_site, ln.supplier_site),
g_proj_avai_bal,
nvl(ln.customer_site, ln.supplier_site),
G_CONS_ADVICE,
nvl(ln.customer_site, ln.supplier_site),
G_PURCHASE_ORDER, ln.customer_site,
G_SALES_ORDER, ln.supplier_site,
G_ASN, ln.supplier_site,
G_SHIP_RECEIPT, ln.customer_site,
G_REPLENISHMENT, ln.supplier_site,
G_REQUISITION, ln.customer_site,
G_PO_ACKNOWLEDGEMENT, ln.supplier_site,
G_WORK_ORDER, ln.supplier_site)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
ln.row_status = G_FAILURE
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
not exists ( select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.supplier_company,G_NULL_STRING))
union select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.customer_company,G_NULL_STRING))
union select 1
from dual
where upper(ln.posting_party_name) =
upper(nvl(ln.publisher_company,G_NULL_STRING))
);
UPDATE msc_supdem_lines_interface ln
SET ln.customer_company = decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_ORDER_FORECAST, null,
G_HIST_SALES,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_company),
G_ALLOC_ONHAND, null,
G_UNALLOCATED_ONHAND, null,
g_safety_stock, NULL,
g_proj_avai_bal, NULL,
G_CONS_ADVICE, NULL,
G_PURCHASE_ORDER, null,
G_SHIP_RECEIPT, null,
G_REQUISITION, null,
ln.customer_company),
ln.customer_site = decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_ORDER_FORECAST, null,
G_HIST_SALES,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.customer_company,
null,
ln.customer_site),
G_ALLOC_ONHAND, null,
G_UNALLOCATED_ONHAND, null,
g_safety_stock, NULL,
g_proj_avai_bal, NULL,
G_PURCHASE_ORDER, null,
G_SHIP_RECEIPT, null,
G_REQUISITION, null,
G_CONS_ADVICE, null,
ln.customer_site),
ln.supplier_company = decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_HIST_SALES,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_company),
G_SUPPLY_COMMIT, null,
G_SUPPLIER_CAP, null,
G_UNALLOCATED_ONHAND, null,
g_safety_stock, NULL,
g_proj_avai_bal, NULL,
G_CONS_ADVICE, null,
G_SALES_ORDER, null,
G_ASN, null,
G_REPLENISHMENT, null,
G_PO_ACKNOWLEDGEMENT, null,
G_WORK_ORDER, null,
ln.supplier_company),
ln.supplier_site = decode(t_order_type(j),
G_SALES_FORECAST,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_HIST_SALES,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_SELL_THRO_FCST,
decode(ln.posting_party_name,
ln.supplier_company,
null,
ln.supplier_site),
G_SUPPLY_COMMIT, null,
G_SUPPLIER_CAP, null,
G_UNALLOCATED_ONHAND, null,
G_CONS_ADVICE, null,
g_safety_stock, NULL,
g_proj_avai_bal, NULL,
G_SALES_ORDER, null,
G_ASN, null,
G_REPLENISHMENT, null,
G_PO_ACKNOWLEDGEMENT, null,
G_WORK_ORDER, null,
ln.supplier_site)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
ln.row_status = G_FAILURE
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id1(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.publisher_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.publisher_company));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.publisher_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(ln.publisher_site));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NULL AND
ln.supplier_company IS NULL AND
not exists (SELECT 1
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language AND
flv.lookup_code in (G_UNALLOCATED_ONHAND,G_CONS_ADVICE,g_work_order,g_safety_stock,g_proj_avai_bal));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
(ln.supplier_company IS NOT NULL or
ln.customer_company IS NOT NULL) and
(G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language) OR
G_CONS_ADVICE = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language) OR
g_safety_stock = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language) OR
g_proj_avai_bal = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c,
msc_companies c1,
msc_company_relationships r
WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company,
ln.posting_party_name)) and
r.subject_id = c1.company_id and
r.object_id = c.company_id and
r.relationship_type = 1);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.customer_company IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.customer_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.customer_site,
G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.supplier_company IS NOT NULL AND
NOT EXISTS (SELECT c.company_id
FROM msc_companies c,
msc_companies c1,
msc_company_relationships r
WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
r.subject_id = c1.company_id and
r.object_id = c.company_id and
r.relationship_type = 2);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.supplier_company IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.supplier_site,
G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.ship_from_party_name IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.ship_from_party_name) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.ship_from_party_site, G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.ship_to_party_name IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.ship_to_party_name) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.ship_to_party_site, G_NULL_STRING)));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.end_order_publisher_name IS NOT NULL AND
NOT EXISTS (SELECT cs.company_site_id
FROM msc_company_sites cs,
msc_companies c
WHERE UPPER(c.company_name) = UPPER(ln.end_order_publisher_name) AND
c.company_id = cs.company_id AND
UPPER(cs.company_site_name) = UPPER(NVL(ln.end_order_publisher_site, G_NULL_STRING)));
update msc_supdem_lines_interface ln
set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
where ln.parent_header_id = p_header_id and
ln.line_id = t_line_id(j) and
NVL(ln.row_status, G_PROCESS) = G_PROCESS and
ln.sync_indicator = 'R' and
not exists ( select i.inventory_item_id
from msc_items i
where i.item_name = ln.item_name
UNION
select msi.inventory_item_id
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map,
msc_company_sites cs,
msc_companies c
where msi.plan_id = -1 and
msi.item_name = nvl(ln.owner_item_name,
nvl(ln.customer_item_name,
ln.supplier_item_name)) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_type = 3 and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = cs.company_site_id and
UPPER(cs.company_site_name) = UPPER(decode(ln.owner_item_name,
null,
decode(ln.customer_item_name,
null,
nvl(ln.supplier_site,
ln.publisher_site),
nvl(ln.customer_site,
ln.publisher_site)),
ln.publisher_site)) and
cs.company_id = c.company_id and
UPPER(c.company_name) = UPPER(decode(ln.owner_item_name,
null,
decode(ln.customer_item_name,
null,
nvl(ln.supplier_company,
ln.publisher_company),
nvl(ln.customer_company,
ln.publisher_company)),
ln.publisher_company)) and
NVL(part.company_id,1) = c.company_id and
ln.item_name IS NULL
UNION
select mis.inventory_item_id
from msc_item_suppliers mis,
msc_trading_partners mtp,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c,
msc_company_sites cs1
where mis.plan_id = -1 and
mis.supplier_item_name = nvl(ln.owner_item_name,
ln.supplier_item_name) and
mis.organization_id = mtp.sr_tp_id and
mis.sr_instance_id = mtp.sr_instance_id and
mtp.partner_id = map2.tp_key and
map2.map_type = 2 and
map2.company_key = cs1.company_site_id and
cs1.company_id = 1 and
mis.supplier_id = map.tp_key and
mis.supplier_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 2 and
UPPER(c.company_name) = UPPER(decode(ln.owner_item_name, null,
nvl(ln.supplier_company,
ln.publisher_company),
ln.publisher_company)) and
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
UPPER(cs.company_site_name) = UPPER(decode(ln.owner_item_name, null,
nvl(ln.supplier_site,
ln.publisher_site),
ln.publisher_site)) and
cs.company_id = c.company_id AND
ln.item_name IS NULL
UNION
select mic.inventory_item_id
from msc_item_customers mic,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c
where mic.plan_id = -1 and
mic.customer_item_name = nvl(ln.owner_item_name,
ln.customer_item_name) and
mic.customer_id = map.tp_key and
--nvl(mic.customer_site_id, map1.tp_key) = map1.tp_key and
mic.customer_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 1 and
UPPER(c.company_name) = UPPER(decode(ln.owner_item_name, null,
nvl(ln.customer_company,
ln.publisher_company),
ln.publisher_company)) and
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
UPPER(cs.company_site_name) = UPPER(decode(ln.owner_item_name, null,
nvl(ln.customer_site, --bug #4292548
ln.publisher_site),
ln.publisher_site)) and
cs.company_id = c.company_id AND
ln.item_name IS NULL
);
update msc_supdem_lines_interface ln
set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
where ln.parent_header_id = p_header_id and
ln.line_id = t_line_id(j) and
NVL(ln.row_status, G_PROCESS) = G_PROCESS and
not exists ( select msi.inventory_item_id
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map,
msc_company_sites cs,
msc_companies c
where msi.plan_id = -1 and
msi.item_name = ln.item_name and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_type = 3 and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = cs.company_site_id and
UPPER(cs.company_site_name) = upper(ln.publisher_site) and
cs.company_id = c.company_id and
UPPER(c.company_name) = upper(ln.publisher_company) and
c.company_id = 1 and
NVL(part.company_id,1) = c.company_id
union select 1
from msc_companies c
where c.company_id <> 1 and G_CONS_ADVICE <> t_order_type(j) and
UPPER(c.company_name) = upper(ln.publisher_company)
/* For Consumption Advice, Item should be valid in the Org modelled as the customer (publisher) */
union select 1
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map,
msc_company_sites cs,
msc_companies c
where
msi.plan_id = -1 and
msi.item_name = ln.item_name and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_type = 3 and
part.modeled_customer_site_id = map.tp_key and
map.map_type = 3 and
map.company_key = cs.company_site_id and
UPPER(cs.company_site_name) = upper(ln.publisher_site) and
cs.company_id = c.company_id and
UPPER(c.company_name) = upper(ln.publisher_company) and
c.company_id <> 1 and
G_CONS_ADVICE = t_order_type(j) and
msi.consigned_flag = 1 and -- bug 4744103
msi.inventory_planning_code = 7 -- bug 4744108
);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
(G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language)) AND
exists (SELECT 'exists'
FROM msc_companies c0,
msc_company_sites s0,
msc_trading_partners mtp,
msc_trading_partners mtp1,
msc_trading_partner_maps maps,
msc_trading_partner_maps maps1,
msc_trading_partner_maps maps2,
msc_trading_partner_sites mtps,
msc_company_sites cs,
msc_companies c,
msc_company_relationships rel
WHERE rel.relationship_type = 2
AND rel.subject_id = 1
AND Upper(c0.company_name) = Upper(ln.publisher_company)
AND rel.object_id = c0.company_id
AND maps.company_key = rel.relationship_id
AND maps.map_type = 1
AND maps.tp_key = mtp.partner_id
AND s0.company_id = c0.company_id
AND Upper(s0.company_site_name) = Upper(ln.publisher_site)
AND maps1.company_key = s0.company_site_id
AND maps1.map_type = 3
AND mtps.partner_site_id = maps1.tp_key
AND mtps.partner_id = mtp.partner_id
AND mtp1.partner_type = 3
AND mtp1.modeled_supplier_id = mtp.partner_id
AND mtp1.modeled_supplier_site_id = mtps.partner_site_id
AND maps2.tp_key = mtp1.partner_id
AND maps2.map_type = 2
AND cs.company_site_id = maps2.company_key
AND cs.company_id = c.company_id
AND c.company_id = 1);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
(G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = UPPER(ln.order_type) AND
flv.language = p_language)) AND
exists( select 'exist'
from msc_companies c,
msc_company_sites s,
msc_trading_partner_maps m,
msc_trading_partners t
where upper(c.company_name) = upper(ln.publisher_company)
and c.company_id = s.company_id
and upper(s.company_site_name) = upper(ln.publisher_site)
and m.company_key = s.company_site_id
and m.map_type = 2
and m.tp_key = t.partner_id
and t.partner_type = 3
and t.modeled_supplier_id is not null
and t.modeled_supplier_site_id is not NULL);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j)
AND Nvl(ln.row_status, g_process) = g_process
AND g_null_string = (SELECT Nvl(ln1.wip_end_date, g_null_string)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code = g_work_order);
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.wip_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) = (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code = g_work_order);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id
AND ln.line_id = t_line_id(j)
AND Nvl(ln.row_status, g_process) = g_process
AND ln.wip_start_date IS NOT NULL
AND to_date(ln.wip_start_date, p_date_format) >
to_date(ln.wip_end_date, p_date_format);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.new_schedule_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_HIST_SALES,
G_SAFETY_STOCK,
G_ALLOC_ONHAND,
G_PROJ_AVAI_BAL));
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.new_schedule_date,
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_CONS_ADVICE,
G_HIST_SALES,
G_SAFETY_STOCK,
G_ALLOC_ONHAND,
G_PROJ_AVAI_BAL));
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_SUPPLY_COMMIT,
G_ASN,
G_SALES_FORECAST,
G_SALES_ORDER)) AND
G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_SUPPLY_COMMIT,
G_ASN,
G_SALES_FORECAST,
G_SALES_ORDER))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = NVL(ln.receipt_date, ln.ship_date), -- jguo NVL(ln.ship_date, ln.receipt_date),
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_SUPPLY_COMMIT,
G_ASN,
---G_SALES_FORECAST,
G_SALES_ORDER))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
);
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = NVL(ln.ship_date, ln.receipt_date),
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code = G_SALES_FORECAST)
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.key_date IS NOT NULL AND
ln.new_schedule_end_date IS NOT NULL AND
to_date(ln.new_schedule_end_date, p_date_format) <
to_date(ln.key_date, p_date_format);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
ln.ship_date is not null AND
ln.receipt_date is not null AND
to_date(ln.ship_date, p_date_format) >
to_date(ln.receipt_date, p_date_format);
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) = (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_SALES_ORDER)) AND
exists (SELECT 'exists'
FROM msc_sup_dem_entries sd
WHERE Upper(sd.publisher_name) = Upper(ln.publisher_company)
AND Upper(sd.publisher_site_name) = Upper(ln.publisher_site)
AND Upper(sd.customer_name) = Upper(ln.customer_company)
AND Upper(sd.customer_site_name) = Upper(ln.customer_site)
AND (sd.item_name = nvl(ln.item_name, nvl(ln.owner_item_name, nvl(ln.customer_item_name, ln.supplier_item_name))) OR
sd.owner_item_name = nvl(ln.owner_item_name, nvl(ln.item_name, nvl(ln.customer_item_name, ln.supplier_item_name))) OR
sd.customer_item_name = nvl(ln.customer_item_name, nvl(ln.item_name, nvl(ln.owner_item_name, ln.supplier_item_name))) OR
sd.supplier_item_name = nvl(ln.supplier_item_name, nvl(ln.item_name, nvl(ln.owner_item_name, ln.customer_item_name))))
AND Upper(sd.publisher_order_type_desc) = Upper(ln.order_type)
--AND Nvl(sd.order_number, g_null_string) = Nvl(ln.order_identifier, g_null_string)
--AND Nvl(sd.line_number, g_null_string) = Nvl(ln.line_number, g_null_string)
--AND Nvl(sd.release_number, g_null_string) = Nvl(ln.release_number, g_null_string)
AND Nvl(sd.end_order_number, g_null_string) = Nvl(ln.pegging_order_identifier, g_null_string)
AND Nvl(sd.end_order_line_number, g_null_string) = Nvl(ln.ref_line_number, g_null_string)
AND Nvl(sd.end_order_rel_number, g_null_string) = Nvl(ln.ref_release_number, g_null_string)
AND nvl(sd.ack_flag, 'N') = 'Y');
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = G_FAILURE
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = g_process AND
ln.err_msg IS NOT NULL;
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = G_SUCCESS
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = g_process AND
Nvl(ln.err_msg, g_null_string) = g_null_string;
update msc_supdem_lines_interface l
set l.inventory_item_id =
(select i.inventory_item_id
from msc_items i,
msc_supdem_lines_interface ln
where i.item_name = ln.item_name and
ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS
UNION
select msi.inventory_item_id
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map,
msc_company_sites cs,
msc_companies c,
msc_supdem_lines_interface ln
where msi.plan_id = -1 and
ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
msi.item_name = nvl(ln.owner_item_name,
nvl(ln.customer_item_name,
ln.supplier_item_name)) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_type = 3 and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = cs.company_site_id and
cs.company_site_name = decode(ln.owner_item_name,
null,
decode(ln.customer_item_name,
null,
nvl(ln.supplier_site,
ln.publisher_site),
nvl(ln.customer_site,
ln.publisher_site)),
ln.publisher_site) and
cs.company_id = c.company_id and
c.company_name = decode(ln.owner_item_name,
null,
decode(ln.customer_item_name,
null,
nvl(ln.supplier_company,
ln.publisher_company),
nvl(ln.customer_company,
ln.publisher_company)),
ln.publisher_company) and
NVL(part.company_id,1) = c.company_id and
ln.item_name IS NULL
UNION
select mis.inventory_item_id
from msc_item_suppliers mis,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c,
msc_supdem_lines_interface ln
where mis.plan_id = -1 and
ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
mis.supplier_item_name = nvl(ln.owner_item_name,
ln.supplier_item_name) and
mis.supplier_id = map.tp_key and
mis.supplier_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 2 and
c.company_name = decode(ln.owner_item_name, null,
nvl(ln.supplier_company,
ln.publisher_company),
ln.publisher_company) and
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
cs.company_site_name = decode(ln.owner_item_name, null,
nvl(ln.supplier_site,
ln.publisher_site),
ln.publisher_site) and
cs.company_id = c.company_id AND
ln.item_name IS NULL
UNION
select mic.inventory_item_id
from msc_item_customers mic,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c,
msc_supdem_lines_interface ln
where mic.plan_id = -1 and
ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
mic.customer_item_name = nvl(ln.owner_item_name,
ln.customer_item_name) and
mic.customer_id = map.tp_key and
--nvl(mic.customer_site_id, map1.tp_key) = map1.tp_key and
mic.customer_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 1 and
c.company_name = decode(ln.owner_item_name, null,
nvl(ln.customer_company,
ln.publisher_company),
ln.publisher_company) and
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
cs.company_site_name = decode(ln.owner_item_name, null,
nvl(ln.customer_site,
ln.publisher_site),
ln.publisher_site) and
cs.company_id = c.company_id AND
ln.item_name IS NULL
)
where l.parent_header_id = p_header_id AND
l.line_id = t_line_id(j) AND
NVL(l.row_status, G_PROCESS) = G_SUCCESS;
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln1.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND Nvl(Upper(ln1.order_identifier),-99) = Nvl(t_order_number(j),-99)
AND Nvl(Upper(ln1.release_number),-99) = Nvl(t_rel_number(j),-99)
AND Nvl(Upper(ln1.line_number),-99) = Nvl(t_line_number(j),-99)
AND Nvl(Upper(ln1.pegging_order_identifier),-99) = Nvl(t_end_order_number(j),-99)
AND Nvl(Upper(ln1.ref_release_number),-99) = Nvl(t_end_order_rel_number(j),-99)
AND Nvl(Upper(ln1.ref_line_number),-99) = Nvl(t_end_order_line_number(j),-99)
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln2.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)
AND Nvl(Upper(ln2.order_identifier),-99) = Nvl(t_order_number(j),-99)
AND Nvl(Upper(ln2.release_number),-99) = Nvl(t_rel_number(j),-99)
AND Nvl(Upper(ln2.line_number),-99) = Nvl(t_line_number(j),-99)
AND Nvl(Upper(ln2.pegging_order_identifier),-99) = Nvl(t_end_order_number(j),-99)
AND Nvl(Upper(ln2.ref_release_number),-99) = Nvl(t_end_order_rel_number(j),-99)
AND Nvl(Upper(ln2.ref_line_number),-99) = Nvl(t_end_order_line_number(j),-99)));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND Nvl(ln1.order_identifier, '-99') = Nvl(t_order_number(j), '-99')
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)
AND Nvl(ln2.order_identifier, '-99') = Nvl(t_order_number(j), '-99')));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln1.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln2.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln1.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND Nvl(Upper(ln1.bucket_type),1) = Nvl(t_bucket_type(j),1)
AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln2.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)
AND Nvl(Upper(ln2.bucket_type),1) = Nvl(t_bucket_type(j),1)
AND t_key_date(j) = trunc(to_date(ln2.key_date,p_date_format))
));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln1.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND Upper(ln1.bucket_type) = t_bucket_type(j)
AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln2.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)
AND Upper(ln2.bucket_type) = t_bucket_type(j)
AND t_key_date(j) = trunc(to_date(ln2.key_date,p_date_format))
));
UPDATE msc_supdem_lines_interface ln
SET ln.row_status = g_failure,
ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
FROM msc_supdem_lines_interface ln1
WHERE ln1.parent_header_id = p_header_id
AND Upper(ln1.publisher_company) = t_pub(j)
AND Upper(ln1.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln1.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln1.order_type) = t_order_type_desc(j)
AND ln1.inventory_item_id = t_item_id(j)
AND Upper(ln1.bucket_type) = t_bucket_type(j)
AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
AND 1 < (SELECT COUNT(*)
FROM msc_supdem_lines_interface ln2
WHERE ln2.parent_header_id = p_header_id
AND Upper(ln2.publisher_company) = t_pub(j)
AND Upper(ln2.publisher_site) = t_pub_site(j)
AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)
AND Nvl(Upper(ln2.supplier_site),-99) = Nvl(t_supp_site(j),-99)
AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)
AND Nvl(Upper(ln2.customer_site),-99) = Nvl(t_cust_site(j),-99)
AND Upper(ln2.order_type) = t_order_type_desc(j)
AND ln2.inventory_item_id = t_item_id(j)
AND Upper(ln2.bucket_type) = t_bucket_type(j)
AND t_key_date(j) = trunc(to_date(ln2.key_date,p_date_format))
));
OPEN c_delete_supply_commit(p_header_id, p_language);
FETCH c_delete_supply_commit
bulk collect INTO t_del_pub_id,
t_del_pub_site_id,
t_del_cust_id,
t_del_cust_site_id,
t_del_item_id;
CLOSE c_delete_supply_commit;
DELETE FROM msc_sup_dem_entries sd
WHERE sd.publisher_id = t_del_pub_id(j)
AND sd.publisher_site_id = t_del_pub_site_id(j)
AND sd.customer_id = t_del_cust_id(j)
AND sd.customer_site_id = t_del_cust_site_id(j)
AND sd.inventory_item_id = t_del_item_id(j)
AND sd.publisher_order_type = g_supply_commit;
OPEN c_delete_order_forecast(p_header_id, p_language);
FETCH c_delete_order_forecast
bulk collect INTO t_del_pub_id,
t_del_pub_site_id,
t_del_supp_id,
t_del_supp_site_id,
t_del_item_id;
CLOSE c_delete_order_forecast;
DELETE FROM msc_sup_dem_entries sd
WHERE sd.publisher_id = t_del_pub_id(j)
AND sd.publisher_site_id = t_del_pub_site_id(j)
AND sd.supplier_id = t_del_supp_id(j)
AND sd.supplier_site_id = t_del_supp_site_id(j)
AND sd.inventory_item_id = t_del_item_id(j)
AND sd.publisher_order_type = G_ORDER_FORECAST;
END update_errors;
SELECT DECODE(ln.new_schedule_date, NULL, SYSDATE,
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.new_schedule_end_date, NULL, SYSDATE,
to_date(ln.new_schedule_end_date, p_date_format)),
DECODE(ln.receipt_date, NULL, SYSDATE,
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.ship_date, NULL, SYSDATE,
to_date(ln.ship_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL, SYSDATE,
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.request_date, NULL, SYSDATE,
to_date(ln.request_date, p_date_format)),
DECODE(ln.original_promised_date, NULL, SYSDATE,
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.wip_start_date, NULL, SYSDATE,
to_date(ln.wip_start_date, p_date_format)),
DECODE(ln.wip_end_date, NULL, SYSDATE,
to_date(ln.wip_end_date, p_date_format))
INTO x_date1,
x_date2,
x_date3,
x_date4,
x_date5,
x_date6,
x_date7,
x_date8,
x_date9
FROM msc_supdem_lines_interface ln
WHERE ln.parent_header_id = p_header_id and
ln.line_id = p_line_id;
SELECT m.message_text
FROM fnd_new_messages m,
fnd_application a
WHERE m.message_name = msg_name AND
m.language_code = lang AND
a.application_short_name = app_name AND
m.application_id = a.application_id;
SELECT language_code
INTO language
FROM fnd_languages
WHERE nls_language = full_language;
SELECT line_id,
nvl(item_name, nvl(owner_item_name, nvl(supplier_item_name, customer_item_name))),
substrb(err_msg,1,240),
substrb(order_type,1,240),
order_identifier,
release_number,
line_number
FROM msc_supdem_lines_interface
WHERE parent_header_id = header_id AND
row_status = G_FAILURE;
SELECT min(line_id)
INTO min_line_id
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id;
CURSOR c_delete
(
p_header_id NUMBER,
p_language VARCHAR2,
p_start_line NUMBER,
p_end_line NUMBER,
p_date_format VARCHAR2
) IS
select sd.transaction_id
from msc_supdem_lines_interface ln,
msc_companies c,
msc_company_sites s ,
fnd_lookup_values flv,
msc_sup_dem_entries sd
where ln.parent_header_id = p_header_id and
ln.line_id between p_start_line and p_end_line and
NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
s.company_id = c.company_id and
UPPER(s.company_site_name) = UPPER(ln.publisher_site) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
UPPER(flv.meaning) = UPPER(ln.order_type) and
UPPER(ln.sync_indicator) = 'D' and
sd.publisher_id = c.company_id and
sd.publisher_site_id = s.company_site_id AND
upper(nvl(sd.supplier_name,G_NULL_STRING)) = upper(nvl(ln.supplier_company,
nvl(sd.supplier_name,G_NULL_STRING))) and
upper(nvl(sd.supplier_site_name,G_NULL_STRING)) = upper(nvl(ln.supplier_site,
nvl(sd.supplier_site_name,G_NULL_STRING))) and
upper(nvl(sd.customer_name,G_NULL_STRING)) = upper(nvl(ln.customer_company,
nvl(sd.customer_name,G_NULL_STRING))) and
upper(nvl(sd.customer_site_name,G_NULL_STRING)) = upper(nvl(ln.customer_site,
nvl(sd.customer_site_name,G_NULL_STRING))) and
(
sd.item_name = NVL(ln.item_name,
Nvl(ln.owner_item_name,
Nvl(ln.customer_item_name, ln.supplier_item_name))) or
sd.owner_item_name = NVL(ln.owner_item_name,
Nvl(ln.item_name,
Nvl(ln.customer_item_name,ln.supplier_item_name))) or
sd.customer_item_name = NVL(ln.customer_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.supplier_item_name))) or
sd.supplier_item_name = NVL(ln.supplier_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.customer_item_name)))
) and
sd.publisher_order_type = flv.lookup_code and
sd.publisher_order_type in (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
g_alloc_onhand,
g_unallocated_onhand,
g_proj_avai_bal, --Consigned CVMI Enh : Bug # 4247230
decode(G_CVMI_PROFILE , 'N' , G_CONS_ADVICE , null , G_CONS_ADVICE , -1 ))
and upper(nvl(sd.bucket_type_desc, G_NULL_STRING)) = upper(nvl(ln.bucket_type, Nvl(sd.bucket_type_desc,G_NULL_STRING)))
and sd.key_date between Decode(ln.key_date,NULL,sd.key_date, trunc(to_date(ln.key_date, p_date_format)))
and decode(ln.new_schedule_end_date, NULL, sd.key_date,trunc(to_date(ln.new_schedule_end_date, p_date_format)))
AND Nvl(sd.last_update_login,-1) <> G_DELETED
UNION
select sd.transaction_id
from msc_supdem_lines_interface ln,
msc_companies c,
msc_company_sites s ,
fnd_lookup_values flv,
msc_sup_dem_entries sd
where ln.parent_header_id = p_header_id and
ln.line_id between p_start_line and p_end_line and
NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
s.company_id = c.company_id and
UPPER(s.company_site_name) = UPPER(ln.publisher_site) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
--- flv.meaning = 'Consumption Advice' and
UPPER(flv.meaning) = UPPER(ln.order_type) and
UPPER(ln.sync_indicator) = 'D' and
sd.publisher_id = c.company_id and
sd.publisher_site_id = s.company_site_id and
upper(nvl(sd.supplier_name,G_NULL_STRING)) = upper(nvl(ln.supplier_company,
nvl(sd.supplier_name,G_NULL_STRING))) and
upper(nvl(sd.supplier_site_name,G_NULL_STRING)) = upper(nvl(ln.supplier_site,
nvl(sd.supplier_site_name,G_NULL_STRING))) and
upper(nvl(sd.customer_name,G_NULL_STRING)) = upper(nvl(ln.customer_company,
nvl(sd.customer_name,G_NULL_STRING))) and
upper(nvl(sd.customer_site_name,G_NULL_STRING)) = upper(nvl(ln.customer_site,
nvl(sd.customer_site_name,G_NULL_STRING))) and
(
sd.item_name = NVL(ln.item_name,
Nvl(ln.owner_item_name,
Nvl(ln.customer_item_name, ln.supplier_item_name))) or
sd.owner_item_name = NVL(ln.owner_item_name,
Nvl(ln.item_name,
Nvl(ln.customer_item_name,ln.supplier_item_name))) or
sd.customer_item_name = NVL(ln.customer_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.supplier_item_name))) or
sd.supplier_item_name = NVL(ln.supplier_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.customer_item_name)))
) and
sd.publisher_order_type = flv.lookup_code and
sd.publisher_order_type = G_WORK_ORDER
and sd.key_date = decode(ln.key_date, NULL,sd.key_date, Trunc(To_date(ln.key_date, p_date_format)))
and NVL(sd.order_number, G_NULL_STRING) = NVL(ln.order_identifier,Nvl(sd.order_number,G_NULL_STRING))
AND Nvl(sd.last_update_login,-1) <> G_DELETED
UNION
select sd.transaction_id
from msc_supdem_lines_interface ln,
msc_companies c,
msc_company_sites s ,
fnd_lookup_values flv,
msc_sup_dem_entries sd
where ln.parent_header_id = p_header_id and
ln.line_id between p_start_line and p_end_line and
NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
s.company_id = c.company_id and
UPPER(s.company_site_name) = UPPER(ln.publisher_site) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
UPPER(flv.meaning) = UPPER(ln.order_type) and
UPPER(ln.sync_indicator) = 'D' and
sd.publisher_id = c.company_id and
sd.publisher_site_id = s.company_site_id and
upper(nvl(sd.supplier_name,G_NULL_STRING)) = upper(nvl(ln.supplier_company,
nvl(sd.supplier_name,G_NULL_STRING))) and
upper(nvl(sd.supplier_site_name,G_NULL_STRING)) = upper(nvl(ln.supplier_site,
nvl(sd.supplier_site_name,G_NULL_STRING))) and
upper(nvl(sd.customer_name,G_NULL_STRING)) = upper(nvl(ln.customer_company,
nvl(sd.customer_name,G_NULL_STRING))) and
upper(nvl(sd.customer_site_name,G_NULL_STRING)) = upper(nvl(ln.customer_site,
nvl(sd.customer_site_name,G_NULL_STRING))) and
(
sd.item_name = NVL(ln.item_name,
Nvl(ln.owner_item_name,
Nvl(ln.customer_item_name, ln.supplier_item_name))) or
sd.owner_item_name = NVL(ln.owner_item_name,
Nvl(ln.item_name,
Nvl(ln.customer_item_name,ln.supplier_item_name))) or
sd.customer_item_name = NVL(ln.customer_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.supplier_item_name))) or
sd.supplier_item_name = NVL(ln.supplier_item_name,
Nvl(ln.item_name,
Nvl(ln.owner_item_name, ln.customer_item_name)))
) and
sd.publisher_order_type = flv.lookup_code and
sd.publisher_order_type IN (G_PURCHASE_ORDER,
G_SALES_ORDER,
G_ASN,
G_SHIP_RECEIPT,
G_REQUISITION,
G_PO_ACKNOWLEDGEMENT,
decode(G_CVMI_PROFILE , 'Y' , G_CONS_ADVICE , -1 )) AND --Consigned CVMI Enh
NVL(sd.order_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
nvl(ln.order_identifier,G_NULL_STRING),
G_SALES_ORDER,
nvl(ln.order_identifier,G_NULL_STRING),
G_ASN,
nvl(ln.order_identifier,G_NULL_STRING),
G_SHIP_RECEIPT,
nvl(ln.order_identifier,G_NULL_STRING),
G_REQUISITION,
nvl(ln.order_identifier, G_NULL_STRING),
G_PO_ACKNOWLEDGEMENT,
nvl(ln.order_identifier, g_null_string),
G_CONS_ADVICE,
nvl(ln.order_identifier,G_NULL_STRING),
G_NULL_STRING) AND
NVL(sd.line_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
nvl(ln.line_number, g_null_string),
G_SALES_ORDER,
nvl(ln.line_number, g_null_string),
G_ASN,
nvl(ln.line_number, g_null_string),
G_SHIP_RECEIPT,
Nvl(ln.line_number, g_null_string),
G_REQUISITION,
Nvl(ln.line_number, g_null_string),
G_PO_ACKNOWLEDGEMENT,
Nvl(ln.line_number, g_null_string),
G_CONS_ADVICE,
nvl(ln.line_number, g_null_string),
G_NULL_STRING) AND
NVL(sd.release_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
Nvl(ln.release_number, g_null_string),
G_SALES_ORDER,
Nvl(ln.release_number, g_null_string),
G_ASN,
Nvl(ln.release_number, g_null_string),
G_SHIP_RECEIPT,
Nvl(ln.release_number, g_null_string),
G_REQUISITION,
Nvl(ln.release_number, g_null_string),
G_PO_ACKNOWLEDGEMENT,
Nvl(ln.release_number, g_null_string),
G_CONS_ADVICE,
Nvl(ln.release_number, g_null_string),
G_NULL_STRING) AND
NVL(sd.end_order_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
Nvl(ln.pegging_order_identifier, g_null_string),
G_SALES_ORDER,
Nvl(ln.pegging_order_identifier, g_null_string),
G_ASN,
Nvl(ln.pegging_order_identifier, g_null_string),
G_SHIP_RECEIPT,
Nvl(ln.pegging_order_identifier, g_null_string),
G_REQUISITION,
Nvl(ln.pegging_order_identifier, g_null_string),
G_PO_ACKNOWLEDGEMENT,
Nvl(ln.pegging_order_identifier, g_null_string),
G_CONS_ADVICE,
Nvl(ln.pegging_order_identifier, g_null_string),
G_NULL_STRING) AND
NVL(sd.end_order_rel_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
Nvl(ln.ref_release_number, g_null_string),
G_SALES_ORDER,
Nvl(ln.ref_release_number, g_null_string),
G_ASN,
Nvl(ln.ref_release_number, g_null_string),
G_SHIP_RECEIPT,
Nvl(ln.ref_release_number, g_null_string),
G_REQUISITION,
Nvl(ln.ref_release_number, g_null_string),
G_PO_ACKNOWLEDGEMENT,
Nvl(ln.ref_release_number, g_null_string),
G_CONS_ADVICE,
Nvl(ln.ref_release_number, g_null_string),
G_NULL_STRING) AND
NVL(sd.end_order_line_number, G_NULL_STRING) = Decode(sd.publisher_order_type,
g_purchase_order,
Nvl(ln.ref_line_number, g_null_string),
G_SALES_ORDER,
Nvl(ln.ref_line_number, g_null_string),
G_ASN,
Nvl(ln.ref_line_number, g_null_string),
G_SHIP_RECEIPT,
Nvl(ln.ref_line_number, g_null_string),
G_REQUISITION,
Nvl(ln.ref_line_number, g_null_string),
G_PO_ACKNOWLEDGEMENT,
Nvl(ln.ref_line_number, g_null_string),
G_CONS_ADVICE,
Nvl(ln.ref_line_number, g_null_string),
G_NULL_STRING) and
Nvl(sd.last_update_login,-1) <> G_DELETED;
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
ln.customer_company,
ln.customer_site,
ln.customer_site,
ln.customer_address,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
null,
--ln.end_order_type,
--ln.bucket_type,
flv.meaning,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
null,
null,
null,
/*
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
*/
DECODE(ln.key_date, NULL, NULL,
to_date(ln.key_date, p_date_format)),
DECODE(ln.new_schedule_date, NULL, NULL,
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.ship_date, NULL, NULL,
to_date(ln.ship_date, p_date_format)),
DECODE(ln.receipt_date, NULL, NULL,
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL, NULL,
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.original_promised_date, NULL, NULL,
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.request_date,NULL,NULL,
to_date(ln.request_date,p_date_format)),
Decode(ln.wip_start_date, NULL, NULL,
to_date(ln.wip_start_date,p_date_format)),
Decode(ln.wip_end_date, NULL, NULL,
to_date(ln.wip_end_date,p_date_format)),
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
ln.customer_company IS NULL and
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
flv.language = p_language AND
flv.lookup_code = g_day and
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code = G_WORK_ORDER AND
UPPER(ln.sync_indicator) = 'R';
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
c1.company_name,
c1.company_id,
s1.company_site_name,
s1.company_site_id,
ln.customer_address,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
null,
--ln.end_order_type,
--ln.bucket_type,
flv.meaning,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
null,
null,
null,
/*
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
*/
DECODE(ln.key_date, NULL, NULL,
to_date(ln.key_date, p_date_format)),
DECODE(ln.new_schedule_date, NULL, NULL,
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.ship_date, NULL, NULL,
to_date(ln.ship_date, p_date_format)),
DECODE(ln.receipt_date, NULL, NULL,
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL, NULL,
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.original_promised_date, NULL, NULL,
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.request_date,NULL,NULL,
to_date(ln.request_date,p_date_format)),
Decode(ln.wip_start_date, NULL, NULL,
to_date(ln.wip_start_date,p_date_format)),
Decode(ln.wip_end_date, NULL, NULL,
to_date(ln.wip_end_date,p_date_format)),
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s,
msc_companies c1,
msc_company_sites s1,
msc_company_relationships r
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
ln.customer_company IS NOT NULL AND
UPPER(c1.company_name) = UPPER(ln.customer_company) AND
r.subject_id = c.company_id AND
r.object_id = c1.company_id AND
r.relationship_type = 1 AND
s1.company_id = c1.company_id AND
UPPER(s1.company_site_name) = UPPER(ln.customer_site) AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
flv.language = p_language AND
flv.lookup_code = g_day and
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code = G_WORK_ORDER AND
UPPER(ln.sync_indicator) = 'R';
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
ln.customer_company,
ln.customer_site,
ln.customer_site,
ln.customer_address,
ln.supplier_company,
ln.supplier_company,
ln.supplier_site,
ln.supplier_site,
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
decode(lookup_code, G_CONS_ADVICE,NVL(ln.bucket_type,G_DAY_DESC),
NULL),
NULL,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
/* for bug# 3271374, populate the new_schedule_date as key_date for unallocated OH */
DECODE(ln.new_schedule_date, NULL, NULL,
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.new_schedule_date, NULL, NULL,
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.ship_date, NULL, NULL,
to_date(ln.ship_date, p_date_format)),
DECODE(ln.receipt_date, NULL, NULL,
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL, NULL,
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.original_promised_date, NULL, NULL,
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.request_date,NULL,NULL,
to_date(ln.request_date,p_date_format)),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code IN (g_unallocated_onhand,
G_CONS_ADVICE) AND
UPPER(ln.sync_indicator) = 'R';
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
decode(ln.customer_company, NULL, c.company_name, c1.company_name),
decode(ln.customer_company, NULL, c.company_id, c1.company_id),
decode(ln.customer_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.customer_site, NULL, s.company_site_id, s1.company_site_id),
ln.customer_address,
decode(ln.supplier_company, NULL, c.company_name, c1.company_name),
decode(ln.supplier_company, NULL, c.company_id, c1.company_id),
decode(ln.supplier_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.supplier_site, NULL, s.company_site_id, s1.company_site_id),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
--Fix for bug 2606288 (Default bucket type to day for planning order types)
Decode(flv1.lookup_code,
g_purchase_order, to_number(NULL),
G_SALES_ORDER, to_number(NULL),
G_ASN, to_number(NULL),
G_SHIP_RECEIPT, to_number(NULL),
G_REQUISITION, to_number(NULL),
G_PO_ACKNOWLEDGEMENT, to_number(NULL),
G_DAY),
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
DECODE(ln.key_date, NULL,to_date(NULL),
to_date(ln.key_date, p_date_format)),
DECODE(ln.new_schedule_date, NULL,to_date(NULL),
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.ship_date, NULL,to_date(NULL),
to_date(ln.ship_date, p_date_format)),
DECODE(ln.receipt_date, NULL,to_date(NULL),
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL,to_date(NULL),
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.original_promised_date, NULL,to_date(NULL),
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.request_date,NULL,to_date(NULL),
to_date(ln.request_date,p_date_format)),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s,
msc_companies c1,
msc_company_sites s1,
msc_company_relationships r
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
ln.bucket_type IS NULL AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
UPPER(c1.company_name) = UPPER(NVL(ln.customer_company, ln.supplier_company)) AND
r.subject_id = c.company_id AND
r.object_id = c1.company_id AND
r.relationship_type = DECODE(ln.customer_company,NULL,2,1) AND
s1.company_id = c1.company_id AND
UPPER(s1.company_site_name) = UPPER(NVL(ln.customer_site, ln.supplier_site)) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
/* Added for work order support */
flv1.lookup_code <> G_WORK_ORDER AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
UPPER(ln.sync_indicator) = 'R'
UNION
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
to_number(NULL),
ln.customer_site,
to_number(NULL),
ln.customer_address,
ln.supplier_company,
to_number(NULL),
ln.supplier_site,
to_number(NULL),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
--Fix for bug 2606288 (Default bucket type to day for planning order types)
Decode(flv1.lookup_code,
g_purchase_order, to_number(NULL),
G_SALES_ORDER, to_number(NULL),
G_ASN, to_number(NULL),
G_SHIP_RECEIPT, to_number(NULL),
G_REQUISITION, to_number(NULL),
G_PO_ACKNOWLEDGEMENT, to_number(NULL),
G_DAY),
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
DECODE(ln.key_date, NULL, to_date(NULL),
to_date(ln.key_date, p_date_format)),
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
to_date(ln.new_schedule_date, p_date_format)),
DECODE(ln.ship_date, NULL, to_date(NULL),
to_date(ln.ship_date, p_date_format)),
DECODE(ln.receipt_date, NULL, to_date(NULL),
to_date(ln.receipt_date, p_date_format)),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL),
to_date(ln.new_order_placement_date, p_date_format)),
DECODE(ln.original_promised_date, NULL, to_date(NULL),
to_date(ln.original_promised_date, p_date_format)),
DECODE(ln.request_date,NULL,to_date(NULL),
to_date(ln.request_date,p_date_format)),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
ln.bucket_type IS NULL AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
flv1.lookup_code in (g_safety_stock, g_proj_avai_bal) AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
UPPER(ln.sync_indicator) = 'R';
SELECT DISTINCT ln.line_id,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
decode(ln.customer_company, NULL, c.company_name, c1.company_name),
decode(ln.customer_company, NULL, c.company_id, c1.company_id),
decode(ln.customer_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.customer_site, NULL, s.company_site_id, s1.company_site_id),
ln.customer_address,
decode(ln.supplier_company, NULL, c.company_name, c1.company_name),
decode(ln.supplier_company, NULL, c.company_id, c1.company_id),
decode(ln.supplier_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.supplier_site, NULL, s.company_site_id, s1.company_site_id),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date,
DECODE(flv1.lookup_code, /* sbala ADD CA */
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE, mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, NULL,
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT, -- SBALACHANGE
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, null, decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_ASN,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, trunc(to_date(ln.ship_date,p_date_format)),
decode(ln.ship_date, null, to_date(null), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date,p_date_format))))),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(null), mlb.bucket_date)),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(null), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date,p_date_format))))),
G_PURCHASE_ORDER,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, mlb.bucket_date,
to_date(null)),
G_SHIP_RECEIPT, to_date(null),
G_ORDER_FORECAST,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, mlb.bucket_date,
to_date(null)),
G_REQUISITION,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date,p_date_format)),
2, mlb.bucket_date,
to_date(null)),
DECODE(ln.ship_date, NULL, to_date(null),
trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_PURCHASE_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SUPPLY_COMMIT,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(null), mlb.bucket_date)),
G_ASN,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.receipt_date, null, to_date(null), mlb.bucket_date)),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.receipt_date, p_date_format)),
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(null), decode(ln.ship_date, null, mlb.bucket_date, to_date(null)))),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(null), mlb.bucket_date)),
DECODE(ln.receipt_date, NULL, to_date(NULL),
trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL),
trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL),
trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL),
trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s,
msc_companies c1,
msc_company_sites s1,
msc_company_relationships r
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_DAY AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
UPPER(c1.company_name) = UPPER(NVL(ln.customer_company, ln.supplier_company)) AND
r.subject_id = c.company_id AND
r.object_id = c1.company_id AND
r.relationship_type = DECODE(ln.customer_company,NULL,2,1) AND
s1.company_id = c1.company_id AND
UPPER(s1.company_site_name) = UPPER(NVL(ln.customer_site, ln.supplier_site)) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
/* Added for work order support */
flv1.lookup_code <> g_work_order
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R'
UNION
SELECT DISTINCT ln.line_id,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
to_number(NULL),
ln.customer_site,
to_number(NULL),
ln.customer_address,
ln.supplier_company,
to_number(NULL),
ln.supplier_site,
to_number(NULL),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date,
DECODE(flv1.lookup_code, /* sbala ADD CA */
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE, mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_ASN, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_FORECAST, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_ORDER, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_PURCHASE_ORDER, to_date(NULL),
G_SHIP_RECEIPT, to_date(NULL),
G_ORDER_FORECAST, to_date(NULL),
G_REQUISITION, to_date(NULL),
DECODE(ln.ship_date, NULL, to_date(NULL),
trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST, mlb.bucket_date,
G_PURCHASE_ORDER, mlb.bucket_date,
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_ASN, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_FORECAST, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_ORDER, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
DECODE(ln.receipt_date, NULL, to_date(NULL), trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL), trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL), trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL), trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_DAY AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code in (g_safety_stock, g_proj_avai_bal)
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R';
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
decode(ln.customer_company, NULL, c.company_name, c1.company_name),
decode(ln.customer_company, NULL, c.company_id, c1.company_id),
decode(ln.customer_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.customer_site, NULL, s.company_site_id, s1.company_site_id),
ln.customer_address,
decode(ln.supplier_company, NULL, c.company_name, c1.company_name),
decode(ln.supplier_company, NULL, c.company_id, c1.company_id),
decode(ln.supplier_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.supplier_site, NULL, s.company_site_id, s1.company_site_id),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date, /* sbala ADD CA */
DECODE(flv1.lookup_code,
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE, mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_ASN,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, trunc(to_date(ln.ship_date, p_date_format)),
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date)),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_PURCHASE_ORDER,
decode(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
G_SHIP_RECEIPT, to_date(NULL),
G_ORDER_FORECAST,
decode(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
G_REQUISITION,
decode(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
DECODE(ln.ship_date, NULL, to_date(NULL),
trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2,trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_PURCHASE_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SUPPLY_COMMIT,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
G_ASN,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.receipt_date, p_date_format)),
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL),
decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL)))),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
DECODE(ln.receipt_date, NULL, to_date(NULL), trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL), trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL), trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL), trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s,
msc_companies c1,
msc_company_sites s1,
msc_company_relationships r
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_WEEK AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
UPPER(c1.company_name) = UPPER(NVL(ln.customer_company, ln.supplier_company)) AND
r.subject_id = c.company_id AND
r.object_id = c1.company_id AND
r.relationship_type = DECODE(ln.customer_company,NULL,2,1) AND
s1.company_id = c1.company_id AND
UPPER(s1.company_site_name) = UPPER(NVL(ln.customer_site, ln.supplier_site)) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
/* Added for work order support */
flv1.lookup_code <> g_work_order
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R'
UNION
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
to_number(NULL),
ln.customer_site,
to_number(NULL),
ln.customer_address,
ln.supplier_company,
to_number(NULL),
ln.supplier_site,
to_number(NULL),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date, /* sbala ADD CA */
DECODE(flv1.lookup_code,
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE, mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_ASN, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_FORECAST, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_ORDER, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_PURCHASE_ORDER, to_date(NULL),
G_SHIP_RECEIPT, to_date(NULL),
G_ORDER_FORECAST, to_date(NULL),
G_REQUISITION, to_date(NULL),
DECODE(ln.ship_date, NULL, to_date(NULL),
trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST, mlb.bucket_date,
G_PURCHASE_ORDER, mlb.bucket_date,
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_ASN, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_FORECAST, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_ORDER, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
DECODE(ln.receipt_date, NULL, to_date(NULL), trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL), trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL), trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL), trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_WEEK AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code IN (g_safety_stock, g_proj_avai_bal)
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R';
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
decode(ln.customer_company, NULL, c.company_name, c1.company_name),
decode(ln.customer_company, NULL, c.company_id, c1.company_id),
decode(ln.customer_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.customer_site, NULL, s.company_site_id, s1.company_site_id),
ln.customer_address,
decode(ln.supplier_company, NULL, c.company_name, c1.company_name),
decode(ln.supplier_company, NULL, c.company_id, c1.company_id),
decode(ln.supplier_site, NULL, s.company_site_name, s1.company_site_name),
decode(ln.supplier_site, NULL, s.company_site_id, s1.company_site_id),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date, /* sbala ADD CA */
DECODE(flv1.lookup_code,
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE, mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_ASN,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, trunc(to_date(ln.ship_date, p_date_format)),
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date)),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
decode(ln.ship_date, null, to_date(NULL), decode(ln.receipt_date, null,
mlb.bucket_date,
trunc(to_date(ln.ship_date, p_date_format))))),
G_PURCHASE_ORDER,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
G_SHIP_RECEIPT, to_date(NULL),
G_ORDER_FORECAST,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
G_REQUISITION,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.ship_date, p_date_format)),
2, mlb.bucket_date,
to_date(NULL)),
DECODE(ln.ship_date, NULL, to_date(NULL),
trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_PURCHASE_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
mlb.bucket_date),
G_SUPPLY_COMMIT,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
G_ASN,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, mlb.bucket_date,
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
G_SALES_FORECAST,
DECODE(ln.shipping_control,
1, trunc(to_date(ln.receipt_date, p_date_format)),
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL),
decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL)))),
G_SALES_ORDER,
DECODE(ln.shipping_control,
1, mlb.bucket_date,
2, trunc(to_date(ln.receipt_date, p_date_format)),
decode(ln.receipt_date, null, to_date(NULL), mlb.bucket_date)),
DECODE(ln.receipt_date, NULL, to_date(NULL), trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL), trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL), trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL), trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s,
msc_companies c1,
msc_company_sites s1,
msc_company_relationships r
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_MONTH AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
UPPER(c1.company_name) = UPPER(NVL(ln.customer_company, ln.supplier_company)) AND
r.subject_id = c.company_id AND
r.object_id = c1.company_id AND
r.relationship_type = DECODE(ln.customer_company,NULL,2,1) AND
s1.company_id = c1.company_id AND
UPPER(s1.company_site_name) = UPPER(NVL(ln.customer_site, ln.supplier_site)) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
/* Added for work order support */
flv1.lookup_code <> g_work_order
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R'
UNION
SELECT ln.line_id ,
c.company_name,
c.company_id,
s.company_site_name,
s.company_site_id,
ln.publisher_address,
ln.customer_company,
to_number(null),
ln.customer_site,
to_number(null),
ln.customer_address,
ln.supplier_company,
to_number(null),
ln.supplier_site,
to_number(null),
ln.supplier_address,
ln.ship_from_party_name,
ln.ship_from_party_site,
ln.ship_from_party_address,
ln.ship_to_party_name,
ln.ship_to_party_site,
ln.ship_to_party_address,
ln.ship_to_address,
ln.end_order_publisher_name,
ln.end_order_publisher_site,
flv1.lookup_code,
flv1.meaning,
ln.end_order_type,
ln.bucket_type,
flv.lookup_code,
ln.inventory_item_id,
ln.order_identifier,
ln.line_number,
ln.release_number,
ln.pegging_order_identifier,
ln.ref_line_number,
ln.ref_release_number,
mlb.bucket_date, /* sbala ADD CA */
DECODE(flv1.lookup_code,
G_HIST_SALES, mlb.bucket_date,
G_SAFETY_STOCK, mlb.bucket_date,
G_ALLOC_ONHAND, mlb.bucket_date,
G_UNALLOCATED_ONHAND, mlb.bucket_date,
G_CONS_ADVICE,mlb.bucket_date,
g_proj_avai_bal, mlb.bucket_date,
DECODE(ln.new_schedule_date, NULL, to_date(NULL),
trunc(to_date(ln.new_schedule_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_SELL_THRO_FCST, mlb.bucket_date,
G_SUPPLIER_CAP, mlb.bucket_date,
G_PROJ_SS, mlb.bucket_date,
G_PROJ_ALLOC_AVAIL, mlb.bucket_date,
G_PROJ_UNALLOC_AVAIL, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_ASN, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_FORECAST, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_SALES_ORDER, decode(ln.ship_date, null, to_date(NULL), mlb.bucket_date),
G_PURCHASE_ORDER, to_date(NULL),
G_SHIP_RECEIPT, to_date(NULL),
G_ORDER_FORECAST, to_date(NULL),
G_REQUISITION, to_date(NULL),
DECODE(ln.ship_date, NULL, to_date(NULL), trunc(to_date(ln.ship_date, p_date_format)))),
DECODE(flv1.lookup_code,
G_ORDER_FORECAST, mlb.bucket_date,
G_PURCHASE_ORDER, mlb.bucket_date,
G_SHIP_RECEIPT, mlb.bucket_date,
G_REQUISITION, mlb.bucket_date,
G_SUPPLY_COMMIT, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_ASN, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_FORECAST, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
G_SALES_ORDER, decode(ln.receipt_date, null, to_date(NULL), decode(ln.ship_date, null, mlb.bucket_date, to_date(NULL))),
DECODE(ln.receipt_date, NULL, to_date(NULL), trunc(to_date(ln.receipt_date, p_date_format)))),
DECODE(ln.new_order_placement_date, NULL, to_date(NULL), trunc(to_date(ln.new_order_placement_date, p_date_format))),
DECODE(ln.original_promised_date, NULL, to_date(NULL), trunc(to_date(ln.original_promised_date, p_date_format))),
DECODE(ln.request_date, NULL, to_date(NULL), trunc(to_date(ln.request_date, p_date_format))),
NULL,
NULL,
round(ln.quantity, 6),
ln.uom,
ln.comments,
ln.carrier_code,
ln.bill_of_lading_number,
ln.tracking_number,
ln.vehicle_number,
ln.container_type,
round(ln.container_qty, 6),
ln.serial_number,
ln.attachment_url,
ln.version,
ln.designator,
ln.context,
ln.attribute1,
ln.attribute2,
ln.attribute3,
ln.attribute4,
ln.attribute5,
ln.attribute6,
ln.attribute7,
ln.attribute8,
ln.attribute9,
ln.attribute10,
ln.attribute11,
ln.attribute12,
ln.attribute13,
ln.attribute14,
ln.attribute15,
ln.posting_party_name
FROM msc_supdem_lines_interface ln,
MSC_LOAD_BUCKETS_TEMP mlb,
fnd_lookup_values flv,
fnd_lookup_values flv1,
msc_companies c,
msc_company_sites s
WHERE ln.parent_header_id = p_header_id AND
ln.line_id between p_start_line and p_end_line AND
flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
flv.language = p_language AND
flv.lookup_code = G_MONTH AND
NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
s.company_id = c.company_id AND
UPPER(s.company_site_name) = UPPER(ln.publisher_site) AND
flv1.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv1.language = p_language AND
UPPER(flv1.meaning) = UPPER(ln.order_type) AND
flv1.lookup_code in (g_safety_stock, g_proj_avai_bal)
AND mlb.parent_header_id = ln.parent_header_id
AND mlb.line_id = ln.line_id
AND UPPER(ln.sync_indicator) = 'R';
select sd.inventory_item_id,
sd.primary_quantity,
to_char(sd.receipt_date,NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'),'DD/MM/YYYY')),
nvl(sd.customer_id, sd.publisher_id),
nvl(sd.customer_site_id, sd.publisher_site_id),
nvl(sd.supplier_id, sd.publisher_id),
nvl(sd.supplier_site_id, sd.publisher_site_id),
sd.primary_uom
from msc_sup_dem_entries sd
where sd.ref_header_id = p_header_id and
sd.publisher_order_type = G_REQUISITION;
SELECT DISTINCT u.user_name
, u.user_id
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_responsibility r,
msc_company_users cu,
msc_companies c,
msc_supdem_lines_interface ln
WHERE ln.parent_header_id = p_header_id
AND ln.publisher_company = c.company_name
AND cu.company_id = c.company_id
AND cu.user_id = u.user_id
AND g.user_id = u.user_id
AND g.responsibility_id = r.responsibility_id
AND r.responsibility_key = 'MSCX_SC_ADMIN_FULL';
SELECT ln.parent_header_id,
ln.line_id,
to_char(trunc(to_date(ln.key_date,l_date_format)),'J') from_date,
to_char(trunc(to_date(nvl(ln.key_end_date,key_date),l_date_format)),'J') to_date
FROM msc_supdem_lines_interface ln,
fnd_lookup_values flv,
fnd_lookup_values flv1
WHERE ln.key_date is not null
and ln.parent_header_id = p_header_id
and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
and flv.language = p_language
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and upper(flv.meaning) = upper(ln.order_type)
and flv.lookup_code <> g_work_order
and flv1.language = flv.language
and flv1.lookup_type = 'MSC_X_BUCKET_TYPE'
and flv1.lookup_code = p_bucket_type
and UPPER(flv1.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING)
and upper(ln.sync_indicator) = 'R'
order by parent_header_id,line_id;
SELECT distinct u.user_name,
l.created_by
INTO l_user_name,
l_user_id
FROM fnd_user u,
msc_supdem_lines_interface l
WHERE u.user_id = l.created_by and
l.parent_header_id = p_header_id;
SELECT language_code
INTO l_language_code
FROM fnd_languages
WHERE nls_language = l_language;
'select meaning from FND_LOOKUP_VALUES '
|| ' where LOOKUP_TYPE = ''MSC_X_BUCKET_TYPE'' '
|| ' and LOOKUP_CODE = 1 '
|| ' and LANGUAGE = :l_language_code '
into G_DAY_DESC
USING l_language_code;
SELECT file_name
INTO l_file_name
FROM msc_files
WHERE plan_id = -1 AND
header_id = p_header_id;
SELECT min(line_id), max(line_id)
INTO l_min, l_max
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id;
update_errors(p_header_id, l_language_code, p_build_err,l_date_format, l_consumption_advice_exists);
log_debug('Opening c_delete');
OPEN c_delete(p_header_id, l_language_code, l_start_line, l_end_line,l_date_format);
FETCH c_delete BULK COLLECT INTO t_transaction_id;
CLOSE c_delete;
UPDATE msc_sup_dem_entries msde
SET msde.quantity = 0,
msde.tp_quantity = 0,
msde.primary_quantity = 0,
msde.ref_header_id = p_header_id,
msde.last_refresh_number = msc_cl_refresh_s.nextval,
msde.last_update_login = G_DELETED
WHERE msde.transaction_id = t_transaction_id(i);
UPDATE msc_serial_numbers msn
SET msn.disable_date = sysdate
WHERE msn.serial_txn_id = t_transaction_id(i) AND
NVL(msn.disable_date,sysdate+1) > sysdate ;
log_debug('After c_delete');
insert into MSC_LOAD_BUCKETS_TEMP
(PARENT_HEADER_ID,
LINE_ID,
BUCKET_DATE,
BUCKET_TYPE)
values ( t_header_id_lst(i),
t_line_id_lst(i),
to_date(lv_new_date,'J'),
1);
insert into MSC_LOAD_BUCKETS_TEMP
(PARENT_HEADER_ID,
LINE_ID,
BUCKET_DATE,
BUCKET_TYPE)
values ( t_header_id_lst(i),
t_line_id_lst(i),
to_date(lv_new_date,'J'),
2);
insert into MSC_LOAD_BUCKETS_TEMP
(PARENT_HEADER_ID,
LINE_ID,
BUCKET_DATE,
BUCKET_TYPE)
values ( t_header_id_lst(i),
t_line_id_lst(i),
to_date(lv_new_date,'J') ,
3);
SELECT 1 INTO l_error_count
FROM dual
WHERE exists(
SELECT 'exists'
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id AND
row_status IN (G_PROCESS,G_FAILURE)
);
SELECT err_msg INTO p_err_msg
FROM msc_supdem_lines_interface
WHERE parent_header_id = p_header_id;
select 1
into l_comp_avg_dmd
from dual
where exists (
select 'exists'
from msc_supdem_lines_interface ln,
fnd_lookup_values flv
where ln.parent_header_id = p_header_id
and ln.row_status = G_SUCCESS
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and flv.lookup_code = 2
and UPPER(flv.meaning) = UPPER(ln.order_type)
);
/* Delete the work orders that have been deleted */
BEGIN
delete from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.publisher_order_type = G_WORK_ORDER
and sd.last_update_login = G_DELETED;
select 1
into l_order_type_flag
from dual
where exists (
select 'exists'
from msc_supdem_lines_interface ln,
fnd_lookup_values flv
where ln.parent_header_id = p_header_id
and ln.row_status = G_SUCCESS
and flv.lookup_type = 'MSC_X_ORDER_TYPE'
and flv.lookup_code IN (2, 3)
and UPPER(flv.meaning) = UPPER(ln.order_type)
);
SELECT c.company_name,
c.company_id,
cs.company_site_name,
cs.company_site_id
INTO t_cust(j),
t_cust_id(j),
t_cust_site(j),
t_cust_site_id(j)
FROM msc_trading_partners mtp,
msc_trading_partners mtp1,
msc_trading_partner_maps maps,
msc_trading_partner_maps maps1,
msc_trading_partner_maps maps2,
msc_trading_partner_sites mtps,
msc_company_sites cs,
msc_companies c,
msc_company_relationships rel
WHERE rel.relationship_type = 2
AND rel.subject_id = 1
AND rel.object_id = t_supp_id(j)
AND maps.company_key = rel.relationship_id
AND maps.map_type = 1
AND maps.tp_key = mtp.partner_id
AND maps1.company_key = t_supp_site_id(j)
AND maps1.map_type = 3
AND mtps.partner_site_id = maps1.tp_key
AND mtps.partner_id = mtp.partner_id
AND mtp1.partner_type = 3
AND mtp1.modeled_supplier_id = mtp.partner_id
AND mtp1.modeled_supplier_site_id = mtps.partner_site_id
AND maps2.tp_key = mtp1.partner_id
AND maps2.map_type = 2
AND cs.company_site_id = maps2.company_key
AND cs.company_id = c.company_id
AND c.company_id = 1;
t_ship_date(j) := msc_x_util.update_ship_rcpt_dates(
t_cust_id(j),
t_cust_site_id(j),
t_supp_id(j),
t_supp_site_id(j),
t_order_type(j),
t_item_id(j),
null,
t_receipt_date(j)
);
t_receipt_date(j) := msc_x_util.update_ship_rcpt_dates(
t_cust_id(j),
t_cust_site_id(j),
t_supp_id(j),
t_supp_site_id(j),
t_order_type(j),
t_item_id(j),
t_ship_date(j),
null
);
SELECT flv.lookup_code
INTO t_end_order_type(j)
FROM fnd_lookup_values flv,
msc_supdem_lines_interface ln
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language_code and
UPPER(flv.meaning) = UPPER(ln.end_order_type) and
ln.parent_header_id = p_header_id and
ln.line_id = t_line_id(j);
SELECT c.company_id
INTO t_end_ord_pub_id(j)
FROM msc_companies c,
msc_supdem_lines_interface l
WHERE UPPER(c.company_name) = UPPER(l.end_order_publisher_name) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT c.company_id
INTO t_shipfrom_id(j)
FROM msc_companies c,
msc_supdem_lines_interface l
WHERE UPPER(c.company_name) = UPPER(l.ship_from_party_name) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT c.company_id
INTO t_shipto_id(j)
FROM msc_companies c,
msc_supdem_lines_interface l
WHERE UPPER(c.company_name) = UPPER(l.ship_to_party_name) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT s.company_site_id
INTO t_end_ord_pub_site_id(j)
FROM msc_company_sites s,
msc_supdem_lines_interface l
WHERE s.company_id = t_end_ord_pub_id(j) AND
UPPER(s.company_site_name) = UPPER(l.end_order_publisher_site) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT s.company_site_id
INTO t_shipfrom_site_id(j)
FROM msc_company_sites s,
msc_supdem_lines_interface l
WHERE s.company_id = t_shipfrom_id(j) AND
UPPER(s.company_site_name) = UPPER(l.ship_from_party_site) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT s.company_site_id
INTO t_shipto_site_id(j)
FROM msc_company_sites s,
msc_supdem_lines_interface l
WHERE s.company_id = t_shipto_id(j) AND
UPPER(s.company_site_name) = UPPER(l.ship_to_party_site) AND
l.parent_header_id = p_header_id and
l.line_id = t_line_id(j);
SELECT c.company_id
INTO t_posting_party_id(j)
FROM msc_companies c
WHERE c.company_name = t_posting_party_name(j);
select mis.supplier_item_name,
nvl(mis.description,itm1.description) ,
nvl(mis.uom_code,msi.uom_code),
msi.base_item_id,
itm.item_name,
msi.planner_code
from msc_item_suppliers mis,
msc_system_items msi,
msc_items itm,
msc_items itm1,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_trading_partners mtp,
msc_company_relationships r
where itm.inventory_item_id (+)= msi.base_item_id and
msi.organization_id = mis.organization_id and
msi.sr_instance_id = mis.sr_instance_id and
msi.inventory_item_id = mis.inventory_item_id and
msi.plan_id = mis.plan_id and
mis.plan_id = -1 and
mis.inventory_item_id = p_item_id and
mis.plan_id = -1 and
mis.organization_id = mtp.sr_tp_id and
mis.sr_instance_id = mtp.sr_instance_id and
itm1.inventory_item_id(+) = mis.inventory_item_id and
mtp.partner_id = map2.tp_key and
mtp.partner_type = 3 and
map2.company_key = p_cust_site_id and
map2.map_type = 2 and
mis.supplier_id = map.tp_key and
--Nvl(mis.supplier_site_id, map1.tp_key) = map1.tp_key and
mis.supplier_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.relationship_type = 2 and
r.subject_id = 1 and
r.object_id = p_pub_id and
map1.map_type = 3 and
map1.company_key = p_pub_site_id
order by mis.using_organization_id desc;
select mis.supplier_item_name,
nvl(mis.description,itm.description),
mis.uom_code
from msc_item_suppliers mis,
msc_items itm,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_trading_partners mtp,
msc_company_relationships r
where mis.inventory_item_id = p_item_id and
mis.plan_id = -1 and
mis.organization_id = mtp.sr_tp_id and
mis.sr_instance_id = mtp.sr_instance_id and
itm.inventory_item_id(+) = mis.inventory_item_id and
mtp.partner_id = map2.tp_key and
mtp.partner_type = 3 and
map2.company_key = nvl(p_cust_site_id, p_pub_site_id) and
map2.map_type = 2 and
mis.supplier_id = map.tp_key and
--Nvl(mis.supplier_site_id, map1.tp_key) = map1.tp_key and
mis.supplier_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.relationship_type = 2 and
r.subject_id = 1 and
r.object_id = nvl(p_supp_id, p_pub_id) and
map1.map_type = 3 and
map1.company_key = nvl(p_supp_site_id, p_pub_site_id)
order by mis.using_organization_id desc;
SELECT mis.vmi_flag,
mis.enable_vmi_auto_replenish_flag
FROM msc_trading_partners mtp,
msc_trading_partner_sites mtps,
msc_trading_partners mtp1,
msc_item_suppliers mis
WHERE Upper(mtp.partner_name) = Upper(p_supplier)
AND mtp.partner_type = 1
AND mtps.partner_id = mtp.partner_id
AND Upper(mtps.tp_site_code) = Upper(p_supplier_site)
AND Upper(mtp1.organization_code) = Upper(p_customer_site)
AND mtp1.partner_type = 3
AND mis.plan_id = -1
AND mis.organization_id = mtp1.sr_tp_id
AND mis.sr_instance_id = mtp1.sr_instance_id
AND mis.supplier_id = mtp.partner_id
AND Nvl(mis.supplier_site_id, -99) = Decode(mis.supplier_site_id, NULL, -99, mtps.partner_site_id)
AND mis.inventory_item_id = p_item_id
ORDER BY mis.using_organization_id DESC;
SELECT meaning,to_number(lookup_code)
FROM fnd_lookup_values
WHERE lookup_type = 'MSC_X_SHIPPING_CONTROL'
AND language = p_language_code
AND lookup_code = p_shipping_control_code
;
t_insert_id lineidList := lineidList();
UPDATE msc_sup_dem_entries
SET number1 = primary_quantity
WHERE ROWID IN
(SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type IN (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
G_PROJ_AVAI_BAL) AND
NVL(key_date, sysdate) = NVL(t_key_date(j), sysdate) AND
NVL(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(j), G_NULL_STRING)
UNION ALL
SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type IN (G_PURCHASE_ORDER,
G_SALES_ORDER,
G_ASN,
G_SHIP_RECEIPT,
G_REQUISITION,
G_PO_ACKNOWLEDGEMENT,
G_ALLOC_ONHAND,
g_unallocated_onhand,
G_CONS_ADVICE) AND
NVL(order_number, G_NULL_STRING) = NVL(t_ord_num(j), G_NULL_STRING) AND
NVL(line_number, G_NULL_STRING) = NVL(t_line_num(j), G_NULL_STRING) AND
NVL(release_number, G_NULL_STRING) = NVL(t_rel_num(j), G_NULL_STRING) AND
NVL(end_order_number, G_NULL_STRING) = NVL(t_end_ord(j), G_NULL_STRING) AND
NVL(end_order_rel_number, G_NULL_STRING) = NVL(t_end_rel(j), G_NULL_STRING) AND
NVL(end_order_line_number, G_NULL_STRING) = NVL(t_end_line(j), G_NULL_STRING)
UNION ALL
SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type = G_WORK_ORDER AND
NVL(order_number, G_NULL_STRING) = NVL(t_ord_num(j), G_NULL_STRING)
);
UPDATE msc_sup_dem_entries
SET last_refresh_number = msc_cl_refresh_s.nextval,
ref_header_id = p_header_id,
ref_line_id = t_line_id(j),
quantity = round(nvl(t_quantity(j),0),6),
tp_quantity = get_quantity(t_quantity(j), nvl(t_uom(j),'Ea'), tp_uom_code, inventory_item_id),
primary_quantity = get_quantity(t_quantity(j), nvl(t_uom(j),'Ea'), primary_uom, inventory_item_id),
comments = t_comments(j),
key_date = t_key_date(j),
-- added the following line for bug 3596556
need_by_date = DECODE(t_order_type(j), G_PURCHASE_ORDER, t_key_date(j), NULL),
new_schedule_date = t_new_sched_date(j),
ship_date = t_ship_date(j),
receipt_date = t_receipt_date(j),
new_order_placement_date = t_new_ord_plac_date(j),
original_promised_date = t_orig_prom_date(j),
request_date = t_req_date(j),
posting_party_id = t_posting_party_id(j),
carrier_code = t_carrier_code(j),
vehicle_number = t_vehicle_number(j),
container_type = t_container_type(j),
container_qty = t_container_qty(j),
tracking_number = t_tracking_number(j),
end_order_publisher_id = t_end_ord_pub_id(j),
ship_to_address = t_shipto_addr(j),
ship_from_party_id = t_shipfrom_id(j),
ship_to_party_id = t_shipto_id(j),
ship_to_party_site_id = t_shipto_site_id(j),
ship_to_party_name = t_shipto(j),
ship_to_party_site_name = t_shipto_site(j),
ship_from_party_site_id = t_shipfrom_site_id(j),
ship_from_party_name = t_shipfrom(j),
ship_from_party_site_name = t_shipfrom_site(j),
end_order_publisher_site_id = t_end_ord_pub_site_id(j),
end_order_publisher_site_name = t_end_ord_pub_site(j),
end_order_publisher_name = t_end_order_pub(j),
order_number = t_ord_num(j),
release_number = t_rel_num(j),
line_number = t_line_num(j),
end_order_number = t_end_ord(j),
end_order_rel_number = t_end_rel(j),
end_order_line_number = t_end_line(j),
ship_from_address = t_shipfrom_addr(j),
publisher_address = t_pub_addr(j),
customer_address = t_cust_addr(j),
supplier_address = t_supp_addr(j),
bill_of_lading_number = t_bill_of_lading(j),
serial_number = t_serial_number(j),
attachment_url = t_attach_url(j),
version = t_version(j),
designator = t_designator(j),
context = t_context(j),
attribute1 = t_attribute1(j),
attribute2 = t_attribute2(j),
attribute3 = t_attribute3(j),
attribute4 = t_attribute4(j),
attribute5 = t_attribute5(j),
attribute6 = t_attribute6(j),
attribute7 = t_attribute7(j),
attribute8 = t_attribute8(j),
attribute9 = t_attribute9(j),
attribute10 = t_attribute10(j),
attribute11 = t_attribute11(j),
attribute12 = t_attribute12(j),
attribute13 = t_attribute13(j),
attribute14 = t_attribute14(j),
attribute15 = t_attribute15(j),
posting_party_name = t_posting_party_name(j),
uom_code = nvl(t_uom(j),'Ea'),
last_update_date = sysdate,
last_updated_by = p_user_id
WHERE ROWID IN
(SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type IN (G_SALES_FORECAST,
G_ORDER_FORECAST,
G_SUPPLY_COMMIT,
G_HIST_SALES,
G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_SAFETY_STOCK,
G_INTRANSIT,
g_replenishment,
G_PROJ_AVAI_BAL) AND
NVL(key_date, sysdate) = NVL(t_key_date(j), sysdate) AND
NVL(bucket_type, G_NULL_STRING) = NVL(t_bkt_type(j), G_NULL_STRING)
UNION ALL
SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type IN (G_PURCHASE_ORDER,
G_SALES_ORDER,
G_ASN,
G_SHIP_RECEIPT,
G_REQUISITION,
G_PO_ACKNOWLEDGEMENT,
decode(G_CVMI_PROFILE , 'Y' , G_CONS_ADVICE , -1 )) AND
--Consigned CVMI Enh
NVL(order_number, G_NULL_STRING) = NVL(t_ord_num(j), G_NULL_STRING) AND
NVL(line_number, G_NULL_STRING) = NVL(t_line_num(j), G_NULL_STRING) AND
NVL(release_number, G_NULL_STRING) = NVL(t_rel_num(j), G_NULL_STRING) AND
NVL(end_order_number, G_NULL_STRING) = NVL(t_end_ord(j), G_NULL_STRING) AND
NVL(end_order_rel_number, G_NULL_STRING) = NVL(t_end_rel(j), G_NULL_STRING) AND
NVL(end_order_line_number, G_NULL_STRING) = NVL(t_end_line(j), G_NULL_STRING)
UNION ALL
SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type = G_WORK_ORDER AND
NVL(order_number, G_NULL_STRING) = NVL(t_ord_num(j), G_NULL_STRING)
UNION ALL
SELECT ROWID FROM MSC_SUP_DEM_ENTRIES
WHERE plan_id = G_PLAN_ID AND
sr_instance_id = G_SR_INSTANCE_ID AND
(nvl(quantity,0) <> 0 OR (Nvl(quantity,0) = 0 AND t_order_type(j) IN (G_ORDER_FORECAST, G_SUPPLY_COMMIT))) AND
publisher_id = t_pub_id(j) AND
publisher_site_id = t_pub_site_id(j) AND
NVL(customer_id, G_NULL_STRING) = NVL(t_cust_id(j), G_NULL_STRING) AND
NVL(customer_site_id, G_NULL_STRING) = NVL(t_cust_site_id(j), G_NULL_STRING) AND
NVL(supplier_id, G_NULL_STRING) = NVL(t_supp_id(j), G_NULL_STRING) AND
NVL(supplier_site_id, G_NULL_STRING) = NVL(t_supp_site_id(j), G_NULL_STRING) AND
publisher_order_type = t_order_type(j) AND
inventory_item_id = t_item_id(j) AND
publisher_order_type IN (G_ALLOC_ONHAND,
G_UNALLOCATED_ONHAND)
);
t_insert_id.EXTEND;
t_insert_id(t_insert_id.COUNT) := j;
t_ins_line_id(t_insert_id.COUNT) := t_line_id(j);
t_ins_pub(t_insert_id.COUNT) := t_pub(j);
t_ins_pub_id(t_insert_id.COUNT) := t_pub_id(j);
t_ins_pub_site(t_insert_id.COUNT) := t_pub_site(j);
t_ins_pub_site_id(t_insert_id.COUNT) := t_pub_site_id(j);
t_ins_pub_addr(t_insert_id.COUNT) := t_pub_addr(j);
t_ins_cust(t_insert_id.COUNT) := t_cust(j);
t_ins_cust_id(t_insert_id.COUNT) := t_cust_id(j);
t_ins_cust_site(t_insert_id.COUNT) := t_cust_site(j);
t_ins_cust_site_id(t_insert_id.COUNT) := t_cust_site_id(j);
t_ins_cust_addr(t_insert_id.COUNT) := t_cust_addr(j);
t_ins_supp(t_insert_id.COUNT) := t_supp(j);
t_ins_supp_id(t_insert_id.COUNT) := t_supp_id(j);
t_ins_supp_site(t_insert_id.COUNT) := t_supp_site(j);
t_ins_supp_site_id(t_insert_id.COUNT) := t_supp_site_id(j);
t_ins_supp_addr(t_insert_id.COUNT) := t_supp_addr(j);
t_ins_shipfrom(t_insert_id.COUNT) := t_shipfrom(j);
t_ins_shipfrom_id(t_insert_id.COUNT) := t_shipfrom_id(j);
t_ins_shipfrom_site(t_insert_id.COUNT) := t_shipfrom_site(j);
t_ins_shipfrom_site_id(t_insert_id.COUNT) := t_shipfrom_site_id(j);
t_ins_shipfrom_addr(t_insert_id.COUNT) := t_shipfrom_addr(j);
t_ins_shipto(t_insert_id.COUNT) := t_shipto(j);
t_ins_shipto_id(t_insert_id.COUNT) := t_shipto_id(j);
t_ins_shipto_site(t_insert_id.COUNT) := t_shipto_site(j);
t_ins_shipto_site_id(t_insert_id.COUNT) := t_shipto_site_id(j);
t_ins_shipto_party_addr(t_insert_id.COUNT) := t_shipto_party_addr(j);
t_ins_shipto_addr(t_insert_id.COUNT) := t_shipto_addr(j);
t_ins_end_order_pub(t_insert_id.COUNT) := t_end_order_pub(j);
t_ins_end_ord_pub_id(t_insert_id.COUNT) := t_end_ord_pub_id(j);
t_ins_end_ord_pub_site(t_insert_id.COUNT) := t_end_ord_pub_site(j);
t_ins_end_ord_pub_site_id(t_insert_id.COUNT) := t_end_ord_pub_site_id(j);
t_ins_order_type(t_insert_id.COUNT) := t_order_type(j);
t_ins_ot_desc(t_insert_id.COUNT) := t_ot_desc(j);
t_ins_end_order_type(t_insert_id.COUNT) := t_end_order_type(j);
t_ins_end_ot_desc(t_insert_id.COUNT) := t_end_ot_desc(j);
t_ins_bkt_type_desc(t_insert_id.COUNT) := t_bkt_type_desc(j);
t_ins_bkt_type(t_insert_id.COUNT) := t_bkt_type(j);
log_debug('Bkt Type = ' || t_ins_bkt_type(t_insert_id.COUNT));
t_ins_item_id(t_insert_id.COUNT) := t_item_id(j);
t_ins_ord_num(t_insert_id.COUNT) := t_ord_num(j);
t_ins_rel_num(t_insert_id.COUNT) := t_rel_num(j);
t_ins_line_num(t_insert_id.COUNT) := t_line_num(j);
t_ins_end_ord(t_insert_id.COUNT) := t_end_ord(j);
t_ins_end_line(t_insert_id.COUNT) := t_end_line(j);
t_ins_end_rel(t_insert_id.COUNT) := t_end_rel(j);
t_ins_key_date(t_insert_id.COUNT) := t_key_date(j);
t_ins_new_sched_date(t_insert_id.COUNT) := t_new_sched_date(j);
t_ins_ship_date(t_insert_id.COUNT) := t_ship_date(j);
t_ins_receipt_date(t_insert_id.COUNT) := t_receipt_date(j);
t_ins_new_ord_plac_date(t_insert_id.COUNT) := t_new_ord_plac_date(j);
t_ins_orig_prom_date(t_insert_id.COUNT) := t_orig_prom_date(j);
t_ins_req_date(t_insert_id.COUNT) := t_req_date(j);
t_ins_wip_st_date(t_insert_id.COUNT) := t_wip_st_date(j);
t_ins_wip_end_date(t_insert_id.COUNT) := t_wip_end_date(j);
t_ins_uom(t_insert_id.COUNT) := nvl(t_uom(j),'Ea');
t_ins_quantity(t_insert_id.COUNT) := t_quantity(j);
t_ins_comments(t_insert_id.COUNT) := t_comments(j);
t_ins_carrier_code(t_insert_id.COUNT) := t_carrier_code(j);
t_ins_bill_of_lading(t_insert_id.COUNT) := t_bill_of_lading(j);
t_ins_tracking_number(t_insert_id.COUNT) := t_tracking_number(j);
t_ins_vehicle_number(t_insert_id.COUNT) := t_vehicle_number(j);
t_ins_container_type(t_insert_id.COUNT) := t_container_type(j);
t_ins_container_qty(t_insert_id.COUNT) := t_container_qty(j);
t_ins_serial_number(t_insert_id.COUNT) := t_serial_number(j);
t_ins_attach_url(t_insert_id.COUNT) := t_attach_url(j);
t_ins_version(t_insert_id.COUNT) := t_version(j);
t_ins_designator(t_insert_id.COUNT) := t_designator(j);
t_ins_context(t_insert_id.COUNT) := t_context(j);
t_ins_attribute1(t_insert_id.COUNT) := t_attribute1(j);
t_ins_attribute2(t_insert_id.COUNT) := t_attribute2(j);
t_ins_attribute3(t_insert_id.COUNT) := t_attribute3(j);
t_ins_attribute4(t_insert_id.COUNT) := t_attribute4(j);
t_ins_attribute5(t_insert_id.COUNT) := t_attribute5(j);
t_ins_attribute6(t_insert_id.COUNT) := t_attribute6(j);
t_ins_attribute7(t_insert_id.COUNT) := t_attribute7(j);
t_ins_attribute8(t_insert_id.COUNT) := t_attribute8(j);
t_ins_attribute9(t_insert_id.COUNT) := t_attribute9(j);
t_ins_attribute10(t_insert_id.COUNT) := t_attribute10(j);
t_ins_attribute11(t_insert_id.COUNT) := t_attribute11(j);
t_ins_attribute12(t_insert_id.COUNT) := t_attribute12(j);
t_ins_attribute13(t_insert_id.COUNT) := t_attribute13(j);
t_ins_attribute14(t_insert_id.COUNT) := t_attribute14(j);
t_ins_attribute15(t_insert_id.COUNT) := t_attribute15(j);
t_ins_posting_party_name(t_insert_id.COUNT) := t_posting_party_name(j);
t_ins_posting_party_id(t_insert_id.COUNT) := t_posting_party_id(j);
IF (t_ins_order_type(t_insert_id.COUNT) in (g_purchase_order,
g_requisition,
g_alloc_onhand,
g_ship_receipt,
G_ASN)) THEN
OPEN c_vmi_item(t_supp(j),
t_supp_site(j),
t_cust_site(j),
t_item_id(j));
t_ins_vmi_flag(t_insert_id.COUNT) := 1;
t_ins_vmi_flag(t_insert_id.COUNT) := NULL;
t_ins_vmi_flag(t_insert_id.COUNT) := NULL;
select item_name,
description
into l_item_name,
l_desc
from msc_items
where inventory_item_id = t_item_id(j);
t_ins_item_desc(t_insert_id.COUNT) := l_desc;
t_ins_item_name(t_insert_id.COUNT) := l_item_name;
select msi.item_name,
msi.description,
msi.uom_code,
msi.base_item_id,
itm.item_name,
msi.planner_code--Bug 4424426
into l_item_name,
l_desc,
l_uom,
l_base_item_id,
l_base_item_name,
l_planner_code--Bug 4424426
from msc_system_items msi,
msc_items itm,
msc_trading_partners part,
msc_trading_partner_maps map
where msi.inventory_item_id = t_item_id(j) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
msi.plan_id = -1 and
itm.inventory_item_id (+)= msi.base_item_id and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = t_pub_site_id(j) and
nvl(part.company_id,1) = t_pub_id(j);
select distinct mic.customer_item_name,
mic.description,
mic.uom_code,
to_number(null),
to_char(null),
mic.planner_code--Bug 4424426
into l_item_name,
l_desc,
l_uom,
l_base_item_id,
l_base_item_name,
l_planner_code--Bug 4424426
from msc_item_customers mic,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r
where mic.inventory_item_id = t_item_id(j) and
mic.plan_id = -1 and
mic.customer_id = map.tp_key and
--NVL(mic.customer_site_id, map1.tp_key) = map1.tp_key and
mic.customer_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.relationship_type = 1 and
r.subject_id = 1 and
r.object_id = t_pub_id(j) and
map1.map_type = 3 and
map1.company_key = t_pub_site_id(j);
select msi.uom_code,
msi.base_item_id,
itm.item_name
into l_uom,
l_base_item_id,
l_base_item_name
from msc_system_items msi,
msc_items itm,
msc_trading_partners part,
msc_trading_partner_maps map
where itm.inventory_item_id (+)=
msi.base_item_id and
msi.inventory_item_id = t_item_id(j) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
msi.plan_id = -1 and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = Decode(t_supp_id(j),
t_pub_id(j), t_cust_site_id(j),
t_supp_site_id(j)) and
nvl(part.company_id,1) = Decode(t_supp_id(j),
t_pub_id(j), t_cust_id(j),
t_supp_id(j));
t_ins_owner_item_name(t_insert_id.COUNT) := l_item_name;
t_ins_owner_item_desc(t_insert_id.COUNT) := l_desc;
t_ins_pri_uom(t_insert_id.COUNT) := l_uom; --NVL(l_uom, nvl(t_uom(j),'Ea'));
t_ins_base_item_name(t_insert_id.COUNT) := l_base_item_name;
t_ins_base_item_id(t_insert_id.COUNT) := l_base_item_id;
t_ins_planner_code(t_insert_id.COUNT) := l_planner_code; --Bug 4424426
if t_ins_order_type(t_insert_id.COUNT) NOT IN (G_UNALLOCATED_ONHAND, g_safety_stock, g_proj_avai_bal) then
--==================================================================================
--Obtain customer item information
--==================================================================================
BEGIN
select msi.item_name,
msi.description,
msi.uom_code
into l_item_name,
l_desc,
l_uom
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map
where msi.plan_id = -1 and
msi.inventory_item_id = t_item_id(j) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = nvl(t_cust_site_id(j), t_pub_site_id(j)) and
nvl(part.company_id,1) = nvl(t_cust_id(j), t_pub_id(j));
select distinct mic.customer_item_name,
mic.description,
mic.uom_code
into l_item_name,
l_desc,
l_uom
from msc_item_customers mic,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r
where mic.inventory_item_id = t_item_id(j) and
mic.plan_id = -1 and
mic.customer_id = map.tp_key and
--NVL(mic.customer_site_id, map1.tp_key) = map1.tp_key and
mic.customer_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.relationship_type = 1 and
r.subject_id = 1 and
r.object_id = nvl(t_cust_id(j), t_pub_id(j)) and
map1.map_type = 3 and
map1.company_key = nvl(t_cust_site_id(j), t_pub_site_id(j));
t_ins_cust_item_name(t_insert_id.COUNT) := l_item_name;
t_ins_cust_item_desc(t_insert_id.COUNT) := l_desc;
t_ins_ref_uom(t_insert_id.COUNT) := NVL(l_uom, t_ins_pri_uom(t_insert_id.COUNT));
select msi.item_name,
msi.description,
msi.uom_code
into l_item_name,
l_desc,
l_uom
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map
where msi.plan_id = -1 and
msi.inventory_item_id = t_item_id(j) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = nvl(t_supp_site_id(j), t_pub_site_id(j)) and
nvl(part.company_id,1) = nvl(t_supp_id(j), t_pub_id(j)) ;
t_ins_supp_item_name(t_insert_id.COUNT) := l_item_name;
t_ins_supp_item_desc(t_insert_id.COUNT) := l_desc;
t_ins_ref_uom(t_insert_id.COUNT) := NVL(l_uom, t_ins_pri_uom(t_insert_id.COUNT));
t_ins_pri_uom(t_insert_id.COUNT),
t_item_id(j),
l_conversion_found,
l_conversion_rate);
t_ins_pri_qty(t_insert_id.COUNT) := round(t_quantity(j)*l_conversion_rate, 6);
t_ins_pri_qty(t_insert_id.COUNT) := round(t_quantity(j), 6);
IF t_ins_order_type(t_insert_id.COUNT) NOT IN (g_unallocated_onhand, g_safety_stock, g_proj_avai_bal) THEN
msc_x_util.get_uom_conversion_rates(t_uom(j),
t_ins_ref_uom(t_insert_id.COUNT),
t_item_id(j),
l_conversion_found,
l_conversion_rate);
t_ins_tp_quantity(t_insert_id.COUNT) := round(t_quantity(j)*l_conversion_rate, 6);
t_ins_tp_quantity(t_insert_id.COUNT) := round(t_quantity(j), 6);
IF t_ins_order_type(t_insert_id.COUNT) IN ( G_PURCHASE_ORDER
, G_ORDER_FORECAST
, G_REQUISITION
, G_SUPPLY_COMMIT
, G_NEGOTIATED_CAPACITY
, G_PO_ACKNOWLEDGEMENT
, G_SALES_ORDER
, G_SHIP_RECEIPT
, G_ASN
) THEN
IF ((t_ins_supp_id (t_insert_id.COUNT) = 1) OR -- if OEM is supplier or supplier=publisher
(t_ins_supp_id (t_insert_id.COUNT) = t_ins_pub_id (t_insert_id.COUNT))) THEN --- Bug #6274985
OPEN c_shipping_control_meaning (
MSC_X_UTIL.GET_SHIPPING_CONTROL_ID
( NVL(t_ins_cust_id(t_insert_id.COUNT), t_ins_pub_id(t_insert_id.COUNT))
, NVL(t_ins_cust_site_id(t_insert_id.COUNT), t_ins_pub_site_id(t_insert_id.COUNT))
, NVL(t_ins_supp_id(t_insert_id.COUNT), t_ins_pub_id(t_insert_id.COUNT))
, NVL(t_ins_supp_site_id(t_insert_id.COUNT), t_ins_pub_site_id(t_insert_id.COUNT))
)
);
FETCH c_shipping_control_meaning INTO t_ins_shipping_control(t_insert_id.COUNT),
t_ins_shipping_control_code(t_insert_id.COUNT);
log_debug('Before Insert');
if t_insert_id IS NOT NULL and t_insert_id.COUNT > 0 THEN
log_debug('At 6' || ' t_insert_id.COUNT := ' || t_insert_id.COUNT);
FORALL i in t_insert_id.FIRST..t_insert_id.LAST
INSERT INTO msc_sup_dem_entries (
ref_header_id,
ref_line_id,
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_address,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
customer_address,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
supplier_address,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_from_address,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
ship_to_address,
end_order_publisher_name,
end_order_publisher_id,
end_order_publisher_site_name,
end_order_publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
end_order_type,
end_order_type_desc,
tp_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
primary_uom,
primary_quantity,
tp_uom_code,
order_number,
release_number,
line_number,
end_order_number,
end_order_line_number,
end_order_rel_number,
key_date,
need_by_date, -- bug 3596556
new_schedule_date,
ship_date,
receipt_date,
new_order_placement_date,
original_promised_date,
request_date,
/* Added for work order support */
wip_start_date,
wip_end_date,
uom_code,
quantity,
tp_quantity,
comments,
carrier_code,
bill_of_lading_number,
tracking_number,
vehicle_number,
container_type,
container_qty,
serial_number,
attachment_url,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
customer_item_description,
supplier_item_description,
owner_item_description,
version,
designator,
context,
vmi_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
base_item_id,
base_item_name
, shipping_control
, shipping_control_code
,planner_code--Bug 4424426
) VALUES (
p_header_id,
t_ins_line_id(i),
msc_sup_dem_entries_s.nextval,
G_PLAN_ID,
G_SR_INSTANCE_ID,
t_ins_pub(i),
t_ins_pub_id(i),
t_ins_pub_site(i),
t_ins_pub_site_id(i),
t_ins_pub_addr(i),
t_ins_cust(i),
t_ins_cust_id(i),
t_ins_cust_site(i),
t_ins_cust_site_id(i),
t_ins_cust_addr(i),
t_ins_supp(i),
t_ins_supp_id(i),
t_ins_supp_site(i),
t_ins_supp_site_id(i),
t_ins_supp_addr(i),
t_ins_shipfrom(i),
t_ins_shipfrom_id(i),
t_ins_shipfrom_site(i),
t_ins_shipfrom_site_id(i),
t_ins_shipfrom_addr(i),
t_ins_shipto(i),
t_ins_shipto_id(i),
t_ins_shipto_site(i),
t_ins_shipto_site_id(i),
t_ins_shipto_addr(i),
t_ins_end_order_pub(i),
t_ins_end_ord_pub_id(i),
t_ins_end_ord_pub_site(i),
t_ins_end_ord_pub_site_id(i),
t_ins_order_type(i),
t_ins_ot_desc(i),
t_ins_end_order_type(i),
t_ins_end_ot_desc(i),
t_ins_end_ot_desc(i),
t_ins_bkt_type_desc(i),
t_ins_bkt_type(i),
t_ins_item_id(i),
t_ins_pri_uom(i),
t_ins_pri_qty(i),
t_ins_ref_uom(i),
t_ins_ord_num(i),
t_ins_rel_num(i),
t_ins_line_num(i),
t_ins_end_ord(i),
t_ins_end_line(i),
t_ins_end_rel(i),
t_ins_key_date(i),
DECODE(t_ins_order_type(i), G_PURCHASE_ORDER, t_ins_key_date(i), NULL), -- need_by_date bug 3596556
t_ins_new_sched_date(i),
t_ins_ship_date(i),
t_ins_receipt_date(i),
t_ins_new_ord_plac_date(i),
t_ins_orig_prom_date(i),
t_ins_req_date(i),
/* Added for Work order support */
Nvl(t_ins_wip_st_date(i), t_ins_wip_end_date(i)),
t_ins_wip_end_date(i),
t_ins_uom(i),
nvl(t_ins_quantity(i),0),
t_ins_tp_quantity(i),
t_ins_comments(i),
t_ins_carrier_code(i),
t_ins_bill_of_lading(i),
t_ins_tracking_number(i),
t_ins_vehicle_number(i),
t_ins_container_type(i),
t_ins_container_qty(i),
t_ins_serial_number(i),
t_ins_attach_url(i),
msc_cl_refresh_s.nextval,
t_posting_party_name(i),
t_posting_party_id(i),
p_user_id,
sysdate,
p_user_id,
sysdate,
t_ins_item_name(i),
t_ins_owner_item_name(i),
t_ins_cust_item_name(i),
t_ins_supp_item_name(i),
nvl(t_ins_item_desc(i), t_ins_owner_item_desc(i)),
t_ins_cust_item_desc(i),
t_ins_supp_item_desc(i),
t_ins_owner_item_desc(i),
t_ins_version(i),
t_ins_designator(i),
t_ins_context(i),
t_ins_vmi_flag(i),
t_ins_attribute1(i),
t_ins_attribute2(i),
t_ins_attribute3(i),
t_ins_attribute4(i),
t_ins_attribute5(i),
t_ins_attribute6(i),
t_ins_attribute7(i),
t_ins_attribute8(i),
t_ins_attribute9(i),
t_ins_attribute10(i),
t_ins_attribute11(i),
t_ins_attribute12(i),
t_ins_attribute13(i),
t_ins_attribute14(i),
t_ins_attribute15(i),
t_ins_base_item_id(i),
t_ins_base_item_name(i)
, t_ins_shipping_control(i)
, t_ins_shipping_control_code(i)
, t_ins_planner_code(i)--Bug 4424426
);
SELECT to_date(p_string, 'YYYYMMDD HH24MISS')
INTO tmpDate
FROM dual;
SELECT to_char(tmpDate, 'DD/MM/YYYY HH24:MI:SS')
INTO p_string
FROM dual;
t_last_update_date lastupdatedateList; --Fix for bug 5599903
t_last_updated_by lastupdatedbyList;
select sd1.primary_quantity,
sd1.tp_quantity,
sd1.number1,
sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.customer_id,
sd1.customer_site_id,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number,
sd1.last_update_date, --Fix for bug 5599903
sd1.last_updated_by,
ln.sync_indicator, --Fix for bug 6147298
ln.quantity
from msc_sup_dem_entries sd1,
msc_supdem_lines_interface ln
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type = 15 and --ASN
sd1.end_order_type = 13 and --PO
ln.parent_header_id = p_header_id and
sd1.item_name = ln.item_name and
sd1.end_order_number = ln.pegging_order_identifier and
nvl(sd1.end_order_rel_number, -99) = nvl(ln.ref_release_number, -99) and
nvl(sd1.end_order_line_number, -99) = nvl(ln.ref_line_number, -99) and
nvl(sd1.order_number, -99) = nvl(ln.order_identifier, -99) and
nvl(sd1.release_number, -99) = nvl(ln.release_number, -99) and
nvl(sd1.line_number, -99) = nvl(ln.line_number, -99); --- Fix for bug 6147298
select sd1.primary_quantity,
sd1.tp_quantity,
sd1.number1,
sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.customer_id,
sd1.customer_site_id,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number,
sd2.end_order_number,
sd2.end_order_rel_number,
sd2.end_order_line_number
from msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type = 15 and --ASN
sd1.end_order_type = 14 and --SO pegged to the ASN
sd2.plan_id = sd1.plan_id and
sd2.publisher_order_type = 14 and
sd2.end_order_type = 13 and --PO pegged to the SO
sd2.inventory_item_id = sd1.inventory_item_id and
sd2.publisher_id = sd1.publisher_id and
sd2.publisher_site_id = sd1.publisher_site_id and
sd2.customer_id = sd1.customer_id and
sd2.customer_site_id = sd1.customer_site_id and
sd2.order_number = sd1.end_order_number and
nvl(sd2.release_number, -99) = nvl(sd1.end_order_rel_number, -99) and
nvl(sd2.line_number, -99) = nvl(sd1.end_order_line_number,-99);
select sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.supplier_id,
sd1.supplier_site_id,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number
from msc_sup_dem_entries sd1
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type = 16 and --Shipment Receipt
sd1.end_order_type = 15; --ASN
select sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.supplier_id,
sd1.supplier_site_id,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number
from msc_sup_dem_entries sd1
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type = 16 and --Shipment Receipt
sd1.end_order_type = 13; --PO
select sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.supplier_id,
sd1.supplier_site_id,
sd2.order_number,
sd2.release_number,
sd2.line_number
from msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type = 16 and
sd1.end_order_type = 13 and
sd2.plan_id = sd1.plan_id and
sd2.inventory_item_id = sd1.inventory_item_id and
sd2.publisher_id = sd1.supplier_id and
sd2.publisher_site_id = sd1.supplier_site_id and
sd2.customer_id = sd1.publisher_id and
sd2.customer_site_id = sd1.publisher_site_id and
sd2.publisher_order_type = 14 and
sd2.end_order_type = 13 and
sd2.end_order_number = sd1.end_order_number and
nvl(sd2.end_order_rel_number, -99) = nvl(sd1.end_order_rel_number, -99) and
nvl(sd2.end_order_line_number, -99) = nvl(sd1.end_order_line_number, -99);
select sd1.inventory_item_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.new_schedule_date
from msc_sup_dem_entries sd1
where sd1.ref_header_id = p_header_id and
sd1.plan_id = -1 and
sd1.publisher_order_type in (9,10); --Allocated + Unallocated onhand
t_last_update_date,
t_last_updated_by, --Fix for bug 5599903
t_sync_id,
t_del_qty; --Fix for bug 6147298
update msc_sup_dem_entries sd
set sd.number1 = DECODE(t_sync_id(j),'R',sd.primary_quantity,'D',sd.number1) --Fix for bug 6147298
where sd.publisher_order_type = 13 and --PO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.primary_quantity = DECODE(t_sync_id(j),'R',round(sd.primary_quantity - decode(t_number1(j),NULL,t_tp_qty(j),Decode(t_pri_qty(j),0, -t_number1(j), t_tp_qty(j) - (t_number1(j)/Decode(t_pri_qty(j),0,1,t_pri_qty(j)))*t_tp_qty(j))), 6),
'D',round(sd.primary_quantity + t_del_qty(j),6)),
sd.tp_quantity = DECODE(t_sync_id(j),'R',round(sd.tp_quantity - decode(t_number1(j),NULL,t_pri_qty(j),t_pri_qty(j)-t_number1(j)), 6),'D',round(sd.tp_quantity + t_del_qty(j),6)) --Fix for bug 6147298
where sd.publisher_order_type = 13 and --PO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = get_quantity(sd.primary_quantity, sd.primary_uom, sd.uom_code, sd.inventory_item_id),
sd.last_updated_by = t_last_updated_by(j),
sd.last_update_date = t_last_update_date(j) --Fix for bug 5599903
where sd.publisher_order_type = 13 and --PO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.number1 = sd.primary_quantity
where sd.publisher_order_type = 14 and --SO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.end_order_number = t_end_order_num(j) and
nvl(sd.end_order_rel_number, -99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.end_order_line_number, -99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.primary_quantity = round(sd.primary_quantity - decode(t_number1(j),NULL,t_pri_qty(j),t_pri_qty(j)-t_number1(j)), 6),
sd.tp_quantity = round(sd.tp_quantity - decode(t_number1(j), NULL, t_tp_qty(j), Decode(t_pri_qty(j),0,-t_number1(j),t_tp_qty(j) - (t_number1(j)/Decode(t_pri_qty(j),0,1,t_pri_qty(j)))*t_tp_qty(j))), 6)
where sd.publisher_order_type = 14 and --SO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.end_order_number = t_end_order_num(j) and
nvl(sd.end_order_rel_number, -99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.end_order_line_number, -99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = get_quantity(sd.primary_quantity, sd.primary_uom, sd.uom_code, sd.inventory_item_id)
where sd.publisher_order_type = 14 and --SO
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.end_order_number = t_end_order_num(j) and
nvl(sd.end_order_rel_number, -99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.end_order_line_number, -99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.number1 = sd.primary_quantity
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 14 and --SO
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.primary_quantity = round(sd.primary_quantity - decode(t_number1(j),NULL,t_pri_qty(j),t_pri_qty(j)-t_number1(j)), 6),
sd.tp_quantity = round(sd.tp_quantity - decode(t_number1(j), NULL, t_tp_qty(j), Decode(t_pri_qty(j),0, -t_number1(j), t_tp_qty(j) - (t_number1(j)/Decode(t_pri_qty(j),0,1,t_pri_qty(j)))*t_tp_qty(j))), 6)
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 14 and --SO
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = get_quantity(sd.primary_quantity, sd.primary_uom, sd.uom_code, sd.inventory_item_id)
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 14 and --SO
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.customer_id = t_cust_id(j) and
sd.customer_site_id = t_cust_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.number1 = sd.primary_quantity
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 13 and --PO
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num1(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num1(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num1(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.primary_quantity = round(sd.primary_quantity - decode(t_number1(j),NULL,t_tp_qty(j), Decode(t_pri_qty(j),0,-t_number1(j), t_tp_qty(j)-(t_number1(j)/Decode(t_pri_qty(j),0,1,t_pri_qty(j)))*t_tp_qty(j))), 6),
sd.tp_quantity = round(sd.tp_quantity - decode(t_number1(j), NULL, t_pri_qty(j), t_pri_qty(j)-t_number1(j)), 6)
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 13 and --PO
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num1(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num1(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num1(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = get_quantity(sd.primary_quantity, sd.primary_uom, sd.uom_code, sd.inventory_item_id),
sd.last_updated_by = t_last_updated_by(j),
sd.last_update_date = t_last_update_date(j) --Fix for bug 5599903
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 13 and --PO
sd.publisher_id = t_cust_id(j) and
sd.publisher_site_id = t_cust_site_id(j) and
sd.supplier_id = t_pub_id(j) and
sd.supplier_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num1(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num1(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num1(j), -99) and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = 0,
sd.primary_quantity = 0,
sd.tp_quantity = 0,
sd.last_update_login = -99
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 15 and --ASN
sd.publisher_id = t_supp_id(j) and
sd.publisher_site_id = t_supp_site_id(j) and
sd.customer_id = t_pub_id(j) and
sd.customer_site_id = t_pub_site_id(j) and
sd.order_number = t_end_order_num(j) and
nvl(sd.release_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.line_number,-99) = nvl(t_end_ord_line_num(j), -99);
update msc_sup_dem_entries sd
set sd.quantity = 0,
sd.primary_quantity = 0,
sd.tp_quantity = 0,
sd.last_update_login = -99
where sd.inventory_item_id = t_item_id(j) and
sd.plan_id = -1 and
sd.publisher_order_type = 15 and --ASN
sd.end_order_type = 13 and --PO
sd.publisher_id = t_supp_id(j) and
sd.publisher_site_id = t_supp_site_id(j) and
sd.customer_id = t_pub_id(j) and
sd.customer_site_id = t_pub_site_id(j) and
sd.end_order_number = t_end_order_num(j) and
nvl(sd.end_order_rel_number,-99) = nvl(t_end_ord_rel_num(j),-99) and
nvl(sd.end_order_line_number,-99) = nvl(t_end_ord_line_num(j), -99) and
sd.quantity > 0;
UPDATE msc_sup_dem_entries sd
SET sd.quantity = 0,
sd.primary_quantity = 0,
sd.tp_quantity = 0,
sd.last_update_login = -99
WHERE sd.publisher_order_type = 15 and --ASN
sd.plan_id = -1 and
sd.inventory_item_id = t_item_id(j) and
sd.end_order_type = 14 and --SO
sd.publisher_id = t_supp_id(j) and
sd.publisher_site_id = t_supp_site_id(j) and
sd.customer_id = t_pub_id(j) and
sd.customer_site_id = t_pub_site_id(j) and
sd.end_order_number = t_end_order_num(j) and
nvl(sd.end_order_rel_number, -99) = nvl(t_end_ord_rel_num(j), -99) and
nvl(sd.end_order_line_number, -99) = nvl(t_end_ord_line_num(j), -99)and
sd.quantity > 0;
update msc_sup_dem_entries sd
set sd.quantity = 0,
sd.primary_quantity = 0,
sd.tp_quantity = 0,
sd.last_update_login = -99
where sd.inventory_item_id = t_item_id(j) and
sd.publisher_order_type = 16 and
sd.plan_id = -1 and
sd.publisher_id = t_pub_id(j) and
sd.publisher_site_id = t_pub_site_id(j) and
sd.quantity > 0 and
sd.new_schedule_date <= t_extract_date(j); --Fix for bug 2308128
PROCEDURE update_qty_from_ui(
p_item_id IN number,
p_qty IN number,
p_uom IN varchar2,
p_pri_uom IN varchar2,
p_tp_uom IN varchar2,
p_pri_qty OUT NOCOPY number,
p_tp_qty OUT NOCOPY number
) IS
l_conv_found boolean := NULL;
END update_qty_from_ui;
t_last_update_date lastUpdateDate;
t_last_updated_by lastUpdatedBy;
t_last_update_login lastUpdateLogin;
select line_id, nvl(master_item_name,publisher_item_name), order_type,serial_number, err_msg
from msc_st_serial_numbers
where parent_header_id = p_header_id and
row_status = G_FAILURE AND
line_id BETWEEN p_start_line and p_end_line;
SELECT min(line_id),max(line_id)
INTO l_min,l_max
FROM msc_st_serial_numbers
WHERE parent_header_id = p_header_id;
SELECT /*+ parallel(mssn,5) */
line_id
BULK COLLECT INTO t_line_id
FROM msc_st_serial_numbers
WHERE parent_header_id = p_header_id AND
line_id BETWEEN l_start_line and l_end_line;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(mssn.sync_indicator, G_NULL_STRING)) NOT IN ('R','D');
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg ,
-- mssn.err_msg || ' ' || l_err_msg ),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT /*+ parallel(l,5) */
l.lookup_code
FROM fnd_lookup_values l
WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(l.meaning) = NVL(UPPER(mssn.order_type), G_NULL_STRING) AND
l.language = p_language AND
l.lookup_code = G_UNALLOCATED_ONHAND);
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg,
-- mssn.err_msg || ' ' || l_err_msg ),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.master_item_name IS NULL AND
mssn.publisher_item_name IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg ,
-- mssn.err_msg || ' ' || l_err_msg),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.serial_number IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.publisher_id = (SELECT /*+ parallel(mc,5) */
mc.company_id
FROM msc_companies mc
WHERE UPPER(mssn.publisher_company) = UPPER(mc.company_name)
)
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg,
-- mssn.err_msg || ' ' || l_err_msg),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.publisher_id IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.publisher_site_id = (SELECT /*+ parallel(mcs,5) */
mcs.company_site_id
FROM msc_company_sites mcs
WHERE mssn.publisher_id=mcs.company_id AND
UPPER(mssn.publisher_site) = UPPER(mcs.company_site_name)
)
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg,
-- mssn.err_msg || ' ' || l_err_msg ),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.publisher_site_id IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.inventory_item_id = (SELECT /*+ parallel(mi,5) */
mi.inventory_item_id
FROM msc_items mi
WHERE mi.item_name = mssn.master_item_name AND
mssn.master_item_name IS NOT NULL
UNION ALL
SELECT msi.inventory_item_id
FROM msc_system_items msi,
msc_trading_partners mtp,
msc_trading_partner_maps mtpm,
msc_company_sites mcs,
msc_companies mc
WHERE msi.plan_id = -1 AND
mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
msi.item_name = mssn.publisher_item_name AND
msi.organization_id = mtp.sr_tp_id AND
msi.sr_instance_id = mtp.sr_instance_id AND
mtp.partner_type = 3 AND
mtp.partner_id = mtpm.tp_key AND
mtpm.map_type = 2 AND
mtpm.company_key = mcs.company_site_id AND
mcs.company_site_name = mssn.publisher_site AND
mcs.company_id = mc.company_id AND
mc.company_name = mssn.publisher_company AND
NVL(mtp.company_id,1) = mc.company_id AND
mssn.publisher_item_name IS NOT NULL AND
mssn.master_item_name IS NULL
UNION ALL
SELECT mis.inventory_item_id
FROM msc_item_suppliers mis,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c
WHERE mis.plan_id = -1 and
mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mis.supplier_item_name = mssn.publisher_item_name AND
mis.supplier_id = map.tp_key and
--nvl(mis.supplier_site_id, map1.tp_key) = map1.tp_key and
mis.supplier_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 2 and
c.company_name = mssn.publisher_company AND
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
cs.company_site_name = mssn.publisher_site AND
cs.company_id = c.company_id and
mssn.publisher_item_name IS NOT NULL AND
mssn.master_item_name IS NULL
UNION ALL
SELECT mic.inventory_item_id
FROM msc_item_customers mic,
msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships r,
msc_company_sites cs,
msc_companies c
WHERE mic.plan_id = -1 and
mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mic.customer_item_name = mssn.publisher_item_name AND
mic.customer_id = map.tp_key and
--nvl(mic.customer_site_id, map1.tp_key) = map1.tp_key and
mic.customer_site_id = map1.tp_key and
map.map_type = 1 and
map.company_key = r.relationship_id and
r.subject_id = 1 and
r.object_id = c.company_id and
r.relationship_type = 1 and
c.company_name = mssn.publisher_company AND
map1.map_type = 3 and
map1.company_key = cs.company_site_id and
cs.company_site_name = mssn.publisher_site AND
cs.company_id = c.company_id and
mssn.publisher_item_name IS NOT NULL AND
mssn.master_item_name IS NULL
)
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg ,
-- mssn.err_msg || ' ' || l_err_msg),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.inventory_item_id IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg ,
-- mssn.err_msg || ' ' || l_err_msg),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
NOT EXISTS (SELECT msi.serial_number_control_code
FROM msc_system_items msi,
msc_trading_partners mtp,
msc_trading_partner_maps mtpm
WHERE msi.plan_id = -1 AND
msi.inventory_item_id = mssn.inventory_item_id AND
msi.sr_instance_id = mtp.sr_instance_id AND
msi.organization_id = mtp.sr_tp_id AND
mtp.partner_type = 3 AND
mtp.partner_id = mtpm.tp_key AND
mtpm.map_type = 2 AND
mtpm.company_key = mssn.publisher_site_id AND
msi.serial_number_control_code = G_SERIAL_ITEM
);
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.serial_txn_id = (SELECT /*+ parallel(msde,5) */
msde.transaction_id
FROM msc_sup_dem_entries msde
WHERE msde.plan_id = -1 AND
msde.publisher_id = mssn.publisher_id AND
msde.publisher_site_id = mssn.publisher_site_id AND
msde.inventory_item_id = mssn.inventory_item_id AND
msde.publisher_order_type = (SELECT /*+ parallel(flv,5) */
flv.lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
UPPER(flv.meaning) = NVL(UPPER(mssn.order_type), G_NULL_STRING) AND
flv.language = p_language)
)
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS ;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = --DECODE(mssn.err_msg, NULL, l_err_msg,
-- mssn.err_msg || ' ' || l_err_msg ),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.serial_txn_id IS NULL;
UPDATE /*+ parallel(mssn,5) */
msc_st_serial_numbers mssn
SET mssn.err_msg = -- DECODE(mssn.err_msg, NULL, l_err_msg,
-- mssn.err_msg || ' ' || l_err_msg),
substrb(l_err_msg,1,1000),
mssn.row_status = G_FAILURE
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(mssn.sync_indicator, G_NULL_STRING))='D' AND
NOT EXISTS (SELECT /*+ parallel(msn,5) */
msn.serial_txn_id
FROM msc_serial_numbers msn
WHERE msn.serial_txn_id = mssn.serial_txn_id AND
msn.serial_number = mssn.serial_number
);
UPDATE /*+ parallel(msn,5) */
msc_serial_numbers msn
SET msn.disable_date = sysdate
WHERE (msn.serial_txn_id,msn.serial_number) IN
(SELECT /*+ parallel(mssn,5) */
mssn.serial_txn_id,mssn.serial_number
FROM msc_st_serial_numbers mssn
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(mssn.sync_indicator, G_NULL_STRING))='D'
) AND
NVL(msn.disable_date,sysdate+1) > sysdate ;
DELETE FROM msc_serial_numbers msn
WHERE msn.serial_txn_id = (SELECT /*+ parallel(mssn,5) */
mssn.serial_txn_id
FROM msc_st_serial_numbers mssn
WHERE mssn.parent_header_id = p_header_id AND
mssn.line_id = t_line_id(j) AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(mssn.sync_indicator, G_NULL_STRING))='R'
) AND
NVL(msn.disable_date,sysdate+1) > sysdate AND
msn.parent_header_id <> p_header_id ;
SELECT /*+ parallel(mssn,5) */
mssn.line_id,
mssn.serial_txn_id,
mssn.serial_number,
mssn.attachment_url,
mssn.sync_indicator,
mssn.row_status,
mssn.user_defined1,
mssn.user_defined2,
mssn.user_defined3,
mssn.user_defined4,
mssn.user_defined5,
mssn.user_defined6,
mssn.user_defined7,
mssn.user_defined8,
mssn.user_defined9,
mssn.user_defined10,
mssn.creation_date,
mssn.created_by,
mssn.last_update_date,
mssn.last_updated_by,
mssn.last_update_login,
mssn.context,
mssn.attribute1,
mssn.attribute2,
mssn.attribute3,
mssn.attribute4,
mssn.attribute5,
mssn.attribute6,
mssn.attribute7,
mssn.attribute8,
mssn.attribute9,
mssn.attribute10,
mssn.attribute11,
mssn.attribute12,
mssn.attribute13,
mssn.attribute14,
mssn.attribute15
BULK COLLECT INTO
t_line_id1,
t_serial_txn_id,
t_serial_number,
t_attachment_url,
t_sync_indicator,
t_row_status,
t_user_defined1,
t_user_defined2,
t_user_defined3,
t_user_defined4,
t_user_defined5,
t_user_defined6,
t_user_defined7,
t_user_defined8,
t_user_defined9,
t_user_defined10,
t_creation_date,
t_created_by,
t_last_update_date,
t_last_updated_by,
t_last_update_login,
t_context,
t_attribute1,
t_attribute2,
t_attribute3,
t_attribute4,
t_attribute5,
t_attribute6,
t_attribute7,
t_attribute8,
t_attribute9,
t_attribute10,
t_attribute11,
t_attribute12,
t_attribute13,
t_attribute14,
t_attribute15
FROM msc_st_serial_numbers mssn
WHERE mssn.parent_header_id = p_header_id AND
NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
UPPER(NVL(mssn.sync_indicator, G_NULL_STRING))='R' AND
mssn.line_id BETWEEN l_start_line and l_end_line;
INSERT /*+ parallel(msn,5) */
INTO msc_serial_numbers msn
(
serial_txn_id,
serial_number,
plan_id,
attachment_url,
user_defined1,
user_defined2,
user_defined3,
user_defined4,
user_defined5,
user_defined6,
user_defined7,
user_defined8,
user_defined9,
user_defined10,
parent_header_id,
line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES (
t_serial_txn_id(i),
t_serial_number(i),
-1,
t_attachment_url(i),
t_user_defined1(i),
t_user_defined2(i),
t_user_defined3(i),
t_user_defined4(i),
t_user_defined5(i),
t_user_defined6(i),
t_user_defined7(i),
t_user_defined8(i),
t_user_defined9(i),
t_user_defined10(i),
p_header_id,
t_line_id(i),
t_creation_date(i),
t_created_by(i),
t_last_update_date(i),
t_last_updated_by(i),
t_last_update_login(i),
t_context(i),
t_attribute1(i),
t_attribute2(i),
t_attribute3(i),
t_attribute4(i),
t_attribute5(i),
t_attribute6(i),
t_attribute7(i),
t_attribute8(i),
t_attribute9(i),
t_attribute10(i),
t_attribute11(i),
t_attribute12(i),
t_attribute13(i),
t_attribute14(i),
t_attribute15(i)
);
DELETE FROM msc_st_serial_numbers mssn
WHERE NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
mssn.parent_header_id = p_header_id ;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_PURCHASE_ORDER,
G_SHIP_RECEIPT,
G_ORDER_FORECAST,
G_REQUISITION,
G_REPLENISHMENT)) -- bug #4070061
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.receipt_date,
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_PURCHASE_ORDER,
G_SHIP_RECEIPT,
G_ORDER_FORECAST,
G_REQUISITION,
G_REPLENISHMENT)) -- bug #4070061
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_PROJ_SS,
G_PROJ_ALLOC_AVAIL,
G_PROJ_UNALLOC_AVAIL))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.ship_date,
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_SELL_THRO_FCST,
G_SUPPLIER_CAP,
G_PROJ_SS,
G_PROJ_ALLOC_AVAIL,
G_PROJ_UNALLOC_AVAIL))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code = G_PO_ACKNOWLEDGEMENT) AND
G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code = G_PO_ACKNOWLEDGEMENT)
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
);
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.receipt_date,
ln.key_end_date = ln.new_schedule_end_date
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
ln.receipt_date is not null AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code = G_PO_ACKNOWLEDGEMENT)
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
AND c.company_id = 1
) ;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN (G_PURCHASE_ORDER,
-- G_SHIP_RECEIPT,
G_ORDER_FORECAST,
G_REQUISITION
, G_SUPPLY_COMMIT
, G_NEGOTIATED_CAPACITY
, G_PO_ACKNOWLEDGEMENT
, G_SALES_ORDER
))
AND MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
) = 1 -- supplier
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN ( G_SHIP_RECEIPT
, G_ASN
))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.receipt_date,
ln.key_end_date = ln.new_schedule_end_date
, ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN (G_PURCHASE_ORDER,
-- G_SHIP_RECEIPT,
G_ORDER_FORECAST,
G_REQUISITION
, G_SUPPLY_COMMIT
, G_NEGOTIATED_CAPACITY
, G_PO_ACKNOWLEDGEMENT
, G_SALES_ORDER
))
AND MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
) = 1 -- supplier
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.receipt_date,
ln.key_end_date = ln.new_schedule_end_date
, ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN ( G_SHIP_RECEIPT
, G_ASN
))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN ( -- G_SELL_THRO_FCST,
-- G_SUPPLIER_CAP,
-- G_PROJ_SS,
-- G_PROJ_ALLOC_AVAIL,
-- G_PROJ_UNALLOC_AVAIL
G_PURCHASE_ORDER
, G_ORDER_FORECAST
, G_SUPPLY_COMMIT
, G_NEGOTIATED_CAPACITY
, G_PO_ACKNOWLEDGEMENT
, G_REQUISITION
, G_SALES_ORDER
))
AND MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
) = 2 -- customer
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
FROM msc_supdem_lines_interface ln1,
fnd_lookup_values flv
WHERE ln1.parent_header_id = ln.parent_header_id and
ln1.line_id = ln.line_id and
UPPER(flv.meaning) = UPPER(ln1.order_type) and
flv.lookup_type = 'MSC_X_ORDER_TYPE' and
flv.language = p_language and
flv.lookup_code IN ( G_SALES_FORECAST
, G_SELL_THRO_FCST
))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.ship_date,
ln.key_end_date = ln.new_schedule_end_date
, ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN ( -- G_SELL_THRO_FCST,
-- G_SUPPLIER_CAP,
-- G_PROJ_SS,
-- G_PROJ_ALLOC_AVAIL,
-- G_PROJ_UNALLOC_AVAIL
G_PURCHASE_ORDER
, G_ORDER_FORECAST
, G_SUPPLY_COMMIT
, G_NEGOTIATED_CAPACITY
, G_PO_ACKNOWLEDGEMENT
, G_REQUISITION
, G_SALES_ORDER
))
AND MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
) = 2 -- customer
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;
UPDATE msc_supdem_lines_interface ln
SET ln.key_date = ln.ship_date,
ln.key_end_date = ln.new_schedule_end_date
, ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
( NVL(ln.customer_company, ln.publisher_company)
, NVL(ln.customer_site, ln.publisher_site)
, NVL(ln.supplier_company, ln.publisher_company)
, NVL(ln.supplier_site, ln.publisher_site)
)
WHERE ln.parent_header_id = p_header_id AND
ln.line_id = t_line_id(j) AND
NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.language = p_language AND
flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
flv.lookup_code IN ( G_SALES_FORECAST
, G_SELL_THRO_FCST
))
AND EXISTS ( SELECT ln.customer_company
FROM msc_companies c
WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
AND c.company_id = 1
)
;