[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*) INTO l_records
FROM mtl_ci_interface
WHERE process_flag = 1;
Delete_Record IN Varchar2 DEFAULT 'Yes'
) RETURN Number IS
L_Success Number := 1;
SELECT Rowid Row_Id,
Process_Mode,
Customer_Name,
Customer_Number,
Customer_Id,
Customer_Category_Code,
Customer_Category,
Address1,
Address2,
Address3,
Address4,
City,
State,
County,
Country,
Postal_Code,
Address_Id,
trim(Customer_Item_Number) Customer_Item_Number, --5622573
Item_Definition_Level_Desc,
Item_Definition_Level,
Customer_Item_Desc,
Model_Customer_Item_Number,
Model_Customer_Item_Id,
Commodity_Code,
Commodity_Code_Id,
Master_Container_Segment1,
Master_Container_Segment2,
Master_Container_Segment3,
Master_Container_Segment4,
Master_Container_Segment5,
Master_Container_Segment6,
Master_Container_Segment7,
Master_Container_Segment8,
Master_Container_Segment9,
Master_Container_Segment10,
Master_Container_Segment11,
Master_Container_Segment12,
Master_Container_Segment13,
Master_Container_Segment14,
Master_Container_Segment15,
Master_Container_Segment16,
Master_Container_Segment17,
Master_Container_Segment18,
Master_Container_Segment19,
Master_Container_Segment20,
Master_Container,
Master_Container_Item_Id,
Container_Item_Org_Name,
Container_Item_Org_Code,
Container_Item_Org_Id,
Detail_Container_Segment1,
Detail_Container_Segment2,
Detail_Container_Segment3,
Detail_Container_Segment4,
Detail_Container_Segment5,
Detail_Container_Segment6,
Detail_Container_Segment7,
Detail_Container_Segment8,
Detail_Container_Segment9,
Detail_Container_Segment10,
Detail_Container_Segment11,
Detail_Container_Segment12,
Detail_Container_Segment13,
Detail_Container_Segment14,
Detail_Container_Segment15,
Detail_Container_Segment16,
Detail_Container_Segment17,
Detail_Container_Segment18,
Detail_Container_Segment19,
Detail_Container_Segment20,
Detail_Container,
Detail_Container_Item_Id,
Min_Fill_Percentage,
Dep_Plan_Required_Flag,
Dep_Plan_Prior_Bld_Flag,
Inactive_Flag,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Demand_Tolerance_Positive,
Demand_Tolerance_Negative,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date
FROM MTL_CI_INTERFACE
WHERE Process_Flag = 1
AND Process_Mode = 1
OR Process_Mode = 3
AND UPPER(Transaction_Type) = 'CREATE'
ORDER BY Model_Customer_Item_Id, Model_Customer_Item_Number
FOR UPDATE NOWAIT;
Recinfo.Last_Update_Date,
Recinfo.Last_Updated_By,
Recinfo.Creation_Date,
Recinfo.Created_By,
Recinfo.Last_Update_Login,
nvl(Recinfo.Request_Id, fnd_global.conc_request_id),
nvl(Recinfo.Program_Application_Id, fnd_global.prog_appl_id),
nvl(Recinfo.Program_Id, fnd_global.conc_program_id),
nvl(Recinfo.Program_Update_Date, sysdate),
Delete_Record );
Delete_Row('I', Delete_Record,
Recinfo.Row_Id);
UPDATE MTL_CI_INTERFACE MCII
SET MCII.Error_Code = Curr_Error,
MCII.Error_Explanation = substrb(Error_Message,1,235),
MCII.Process_Mode = 2
WHERE MCII.Rowid = Recinfo.Row_Id;
UPDATE MTL_CI_INTERFACE MCII
SET MCII.Error_Code = TO_CHAR(Error_Number),
MCII.Error_Explanation = substrb(Error_Message,1,235),
MCII.Process_Mode = 2
WHERE MCII.Rowid = Recinfo.Row_Id;
UPDATE MTL_CI_INTERFACE MCII
SET MCII.Error_Code = Curr_Error,
MCII.Error_Explanation = substrb(Error_Message,1,235),
MCII.Process_Mode = 2
WHERE MCII.Rowid = Recinfo.Row_Id;
UPDATE MTL_CI_INTERFACE MCII
SET MCII.Error_Code = TO_CHAR(Error_Number),
MCII.Error_Explanation = substrb(Error_Message,1,235),
MCII.Process_Mode = 2
WHERE MCII.Rowid = Recinfo.Row_Id;
Last_Update_Date IN OUT NOCOPY Date,
Last_Updated_By IN OUT NOCOPY Number,
Creation_Date IN OUT NOCOPY Date,
Created_By IN OUT NOCOPY Number,
Last_Update_Login IN OUT NOCOPY Number,
Request_Id IN Number,
Program_Application_Id IN Number,
Program_Id IN Number,
Program_Update_Date IN Date,
Delete_Record IN Varchar2 DEFAULT NULL
) IS
BEGIN
Validate_CI_Def_Level (
Item_Definition_Level, Item_Definition_Level_Desc,
Customer_Id, Customer_Number, Customer_Name,
Customer_Category_Code, Customer_Category,
Address_Id, Address1, Address2, Address3,
Address4, City, State, County, Country, Postal_Code);
Program_Id, Program_Update_Date);
Address_Id, Inactive_Flag, Last_Updated_By,
Last_Update_Date, Created_By, Creation_Date,
NULL, NULL, NULL, NULL);
Insert_Row('I', Last_Update_Date, Last_Updated_By, Creation_Date,
Created_By, Last_Update_Login, Customer_Id,
Customer_Category_Code, Address_Id,
Customer_Item_Number, Item_Definition_Level,
Customer_Item_Desc, Model_Customer_Item_Id,
Commodity_Code_Id, Master_Container_Item_Id,
Container_Item_Org_Id, Detail_Container_Item_Id,
Min_Fill_Percentage, Dep_Plan_Required_Flag,
Dep_Plan_Prior_Bld_Flag, Inactive_Flag,
Attribute_Category, Attribute1, Attribute2,
Attribute3, Attribute4, Attribute5, Attribute6,
Attribute7, Attribute8, Attribute9, Attribute10,
Attribute11, Attribute12, Attribute13, Attribute14,
Attribute15, Demand_Tolerance_Positive,
Demand_Tolerance_Negative, Request_Id,
Program_Application_Id, Program_Id,
Program_Update_Date, NULL, NULL, NULL, NULL);
Delete_Row('I', Delete_Record, Row_Id);
SELECT Lookup_Code, Meaning
INTO Temp_Lookup_Code, Temp_Meaning
FROM MFG_LOOKUPS MFGL
WHERE UPPER(MFGL.Meaning) = UPPER(P_Item_Definition_Level_Desc)
AND MFGL.Lookup_Type = 'INV_ITEM_DEFINITION_LEVEL';
SELECT Customer_Id, Status
INTO Temp_Customer_Id, Temp_Status
FROM RA_CUSTOMERS RAC
WHERE RAC.Customer_Id = P_Customer_Id;
SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
INTO Temp_Customer_Id, Temp_Status
FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = P_Customer_Id;
SELECT Customer_Id, Status
INTO Temp_Customer_Id, Temp_Status
FROM RA_CUSTOMERS RAC
WHERE RAC.Customer_Number = P_Customer_Number;
SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
INTO Temp_Customer_Id, Temp_Status
FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.ACCOUNT_NUMBER = P_Customer_Number;
SELECT Customer_Id, Status
INTO Temp_Customer_Id, Temp_Status
FROM RA_CUSTOMERS RAC
WHERE RAC.Customer_Name = P_Customer_Name;
SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
INTO Temp_Customer_Id, Temp_Status
FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PARTY.PARTY_NAME like P_Customer_Name || '%';
SELECT Address_Id, Status
INTO Temp_Address_Id, Temp_Status
FROM RA_ADDRESSES RAA
WHERE RAA.Address_Id = P_Address_Id
AND RAA.Customer_Id = P_Customer_Id; */
SELECT ACCT_SITE.CUST_ACCT_SITE_ID , ACCT_SITE.STATUS
INTO Temp_Address_Id, Temp_Status
FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = P_Address_Id
AND ACCT_SITE.CUST_ACCOUNT_ID = P_Customer_Id
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
AND NVL(ACCT_SITE.ORG_ID,
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99);
SELECT Address_Id, Status
INTO Temp_Address_Id, Temp_Status
FROM RA_ADDRESSES RAA
WHERE NVL(RAA.Address1, ' ') = NVL(P_Address1, ' ')
AND NVL(RAA.Address2, ' ') = NVL(P_Address2, ' ')
AND NVL(RAA.Address3, ' ') = NVL(P_Address3, ' ')
AND NVL(RAA.Address4, ' ') = NVL(P_Address4, ' ')
AND NVL(RAA.City, ' ') = NVL(P_City, ' ')
AND NVL(RAA.State, ' ') = NVL(P_State, ' ')
AND NVL(RAA.County, ' ') = NVL(P_County, ' ')
AND NVL(RAA.Country, ' ') = NVL(P_Country, ' ')
AND NVL(RAA.Postal_Code, ' ') = NVL(P_Postal_Code, ' ')
AND RAA.Customer_Id = P_Customer_Id; */
SELECT ACCT_SITE.CUST_ACCT_SITE_ID , ACCT_SITE.STATUS
INTO Temp_Address_Id, Temp_Status
FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
AND NVL(ACCT_SITE.ORG_ID,
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)
AND NVL(LOC.Address1, ' ') = NVL(P_Address1, ' ')
AND NVL(LOC.Address2, ' ') = NVL(P_Address2, ' ')
AND NVL(LOC.Address3, ' ') = NVL(P_Address3, ' ')
AND NVL(LOC.Address4, ' ') = NVL(P_Address4, ' ')
AND NVL(LOC.City, ' ') = NVL(P_City, ' ')
AND NVL(LOC.State, ' ') = NVL(P_State, ' ')
AND NVL(LOC.County, ' ') = NVL(P_County, ' ')
AND NVL(LOC.Country, ' ') = NVL(P_Country, ' ')
AND NVL(LOC.Postal_Code, ' ') = NVL(P_Postal_Code, ' ')
AND ACCT_SITE.CUST_ACCOUNT_ID = P_Customer_Id;
SELECT ARL.Lookup_Code, ARL.Enabled_Flag, ARL.Start_Date_Active, ARL.End_Date_Active
INTO Temp_Lookup_Code, Temp_Enabled_Flag, Temp_Start_Date_Active, Temp_End_Date_Active
FROM AR_LOOKUPS ARL
WHERE ARL.Lookup_Code = P_Customer_Category_Code
AND ARL.Lookup_Type = 'ADDRESS_CATEGORY'
AND rownum = 1;
SELECT ARL.Lookup_Code, ARL.Enabled_Flag, ARL.Start_Date_Active, ARL.End_Date_Active
INTO Temp_Lookup_Code, Temp_Enabled_Flag, Temp_Start_Date_Active, Temp_End_Date_Active
FROM AR_LOOKUPS ARL
WHERE UPPER(ARL.Meaning) = UPPER(P_Customer_Category)
AND ARL.Lookup_Type = 'ADDRESS_CATEGORY'
AND rownum = 1;
SELECT segment_num from
fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
order by segment_num;
SELECT Inventory_Item_Id
INTO Temp_Container_Item_Id
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.Inventory_Item_Id = P_Container_Item_Id
AND MSI.Container_Item_Flag = 'Y'
AND MSI.Organization_Id = P_Container_Organization_Id;
SELECT ID_Flex_Num
INTO L_StructNum
FROM fnd_id_flex_structures
WHERE application_id = 401
AND id_flex_code = 'MSTK'
AND upper(enabled_flag) = 'Y';
SELECT ID_Flex_Num
INTO L_StructNum
FROM fnd_id_flex_structures
WHERE application_id = 401
AND id_flex_code = 'MSTK'
AND upper(enabled_flag) = 'Y';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT1';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT2';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT3';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT4';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT5';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT6';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT7';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT8';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT9';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT10';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT11';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT12';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT13';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT14';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT15';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT16';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT17';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT18';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT19';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT20';
SELECT Commodity_Code_Id, Inactive_Date
INTO Temp_Commodity_Code_Id, Temp_Inactive_Date
FROM MTL_COMMODITY_CODES MCC
WHERE MCC.Commodity_Code_Id = P_Commodity_Code_Id;
SELECT Commodity_Code_Id, Inactive_Date
INTO Temp_Commodity_Code_Id, Temp_Inactive_Date
FROM MTL_COMMODITY_CODES MCC
WHERE MCC.Commodity_Code = P_Commodity_Code;
/* Bug 3849821 Select Customer_Item_Id of Model Ct Item entered in the Interface table */
SELECT Customer_Item_Id,
Item_Definition_Level, Inactive_Flag
INTO Temp_Model_Customer_Item_Id,
Temp_Item_Definition_Level, Temp_Inactive_Flag
FROM MTL_CUSTOMER_ITEMS MCI
WHERE MCI.Customer_Item_Id =
P_Model_Customer_Item_Id
AND MCI.Customer_Id =
P_Customer_Id;
SELECT Customer_Item_Id,
Item_Definition_Level, Inactive_Flag
INTO Temp_Model_Customer_Item_Id,
Temp_Item_Definition_Level, Temp_Inactive_Flag
FROM MTL_CUSTOMER_ITEMS MCI
WHERE MCI.Customer_Item_Number =
P_Model_Customer_Item
AND MCI.Customer_Id = P_Customer_Id
AND MCI.Customer_Category_Code =
P_Customer_Category_Code
OR MCI.Address_Id = P_Address_Id;
SELECT count(*) INTO l_records
FROM mtl_ci_xrefs_interface
WHERE process_flag = 1;
Delete_Record IN Varchar2 DEFAULT 'Yes'
) RETURN Number IS
L_Success Number := 1;
SELECT Rowid Row_Id,
Process_Mode,
Customer_Name,
Customer_Number,
Customer_Id,
Customer_Category_Code,
Customer_Category,
Address1,
Address2,
Address3,
Address4,
City,
State,
County,
Country,
Postal_Code,
Address_Id,
Customer_Item_Number,
Item_Definition_Level_Desc,
Item_Definition_Level,
Customer_Item_Id,
Master_Organization_Name,
Master_Organization_Code,
Master_Organization_Id,
Inventory_Item_Segment1,
Inventory_Item_Segment2,
Inventory_Item_Segment3,
Inventory_Item_Segment4,
Inventory_Item_Segment5,
Inventory_Item_Segment6,
Inventory_Item_Segment7,
Inventory_Item_Segment8,
Inventory_Item_Segment9,
Inventory_Item_Segment10,
Inventory_Item_Segment11,
Inventory_Item_Segment12,
Inventory_Item_Segment13,
Inventory_Item_Segment14,
Inventory_Item_Segment15,
Inventory_Item_Segment16,
Inventory_Item_Segment17,
Inventory_Item_Segment18,
Inventory_Item_Segment19,
Inventory_Item_Segment20,
Inventory_Item,
Inventory_Item_Id,
Preference_Number,
Inactive_Flag,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date
FROM MTL_CI_XREFS_INTERFACE
WHERE Process_Flag = 1
AND Process_Mode = 1
OR Process_Mode = 3
AND UPPER(Transaction_Type) = 'CREATE'
FOR UPDATE NOWAIT;
Recinfo2.Last_Update_Date,
Recinfo2.Last_Updated_By,
Recinfo2.Creation_Date,
Recinfo2.Created_By,
Recinfo2.Last_Update_Login,
nvl(Recinfo2.Request_Id, fnd_global.conc_request_id),
nvl(Recinfo2.Program_Application_Id, fnd_global.prog_appl_id),
nvl(Recinfo2.Program_Id, fnd_global.conc_program_id),
nvl(Recinfo2.Program_Update_Date, sysdate),
Delete_Record);
Delete_Row('X', Delete_Record, Recinfo2.Row_Id);
UPDATE MTL_CI_XREFS_INTERFACE MCIXI
SET MCIXI.Error_Code = Curr_Error,
MCIXI.Error_Explanation = substrb(Error_Message,1,235),
MCIXI.Process_Mode = 2
WHERE MCIXI.Rowid = Recinfo2.Row_Id;
UPDATE MTL_CI_XREFS_INTERFACE MCIXI
SET MCIXI.Error_Code = TO_CHAR(Error_Number),
MCIXI.Error_Explanation = substrb(Error_Message,1,235),
MCIXI.Process_Mode = 2
WHERE MCIXI.Rowid = Recinfo2.Row_Id;
UPDATE MTL_CI_XREFS_INTERFACE MCIXI
SET MCIXI.Error_Code = Curr_Error,
MCIXI.Error_Explanation = substrb(Error_Message,1,235),
MCIXI.Process_Mode = 2
WHERE MCIXI.Rowid = Recinfo2.Row_Id;
UPDATE MTL_CI_XREFS_INTERFACE MCIXI
SET MCIXI.Error_Code = TO_CHAR(Error_Number),
MCIXI.Error_Explanation = substrb(Error_Message,1,235),
MCIXI.Process_Mode = 2
WHERE MCIXI.Rowid = Recinfo2.Row_Id;
Last_Update_Date IN OUT NOCOPY Date,
Last_Updated_By IN OUT NOCOPY Number,
Creation_Date IN OUT NOCOPY Date,
Created_By IN OUT NOCOPY Number,
Last_Update_Login IN OUT NOCOPY Number,
Request_Id IN Number,
Program_Application_Id IN Number,
Program_Id IN Number,
Program_Update_Date IN Date,
Delete_Record IN Varchar2 DEFAULT NULL ) IS
BEGIN
Validate_Cust_Item(Customer_Item_Id, Customer_Item_Number, Item_Definition_Level, Item_Definition_Level_Desc, Customer_Id,
Customer_Number, Customer_Name, Customer_Category_Code, Customer_Category, Address_Id, Address1, Address2, Address3,
Address4, City, State, County, Country, Postal_Code);
Check_Required_Columns(NULL, NULL, NULL, NULL, NULL, NULL, Inactive_Flag, Last_Updated_By, Last_Update_Date, Created_By,
Creation_Date, Customer_Item_Id, Inventory_Item_Id, Master_Organization_Id, Preference_Number);
Validate_Concurrent_Program(Request_Id, Program_Application_Id, Program_Id, Program_Update_Date);
Insert_Row(
NULL,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Inactive_Flag,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
NULL,
NULL,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
Customer_Item_Id,
Inventory_Item_Id,
Master_Organization_Id,
Preference_Number);
Delete_Row('X', Delete_Record, Row_Id);
SELECT Customer_Item_Id, Inactive_Flag
INTO Temp_Customer_Item_Id, Temp_Inactive_Flag
FROM MTL_CUSTOMER_ITEMS MCI
WHERE MCI.Customer_Item_Id = P_Customer_Item_Id;
SELECT Customer_Item_Id, Inactive_Flag
INTO Temp_Customer_Item_Id, Temp_Inactive_Flag
FROM MTL_CUSTOMER_ITEMS MCI
WHERE MCI.Item_Definition_Level = NVL(P_Item_Definition_Level, V_Item_Definition_Level)
AND MCI.Customer_Id = NVL(P_Customer_Id, V_Customer_Id)
AND NVL(MCI.Customer_Category_Code, ' ') = NVL(NVL(P_Customer_Category_Code, V_Customer_Category_Code), ' ')
AND NVL(MCI.Address_Id, -99) = NVL(NVL(P_Address_Id, V_Address_Id), -99)
AND MCI.Customer_Item_Number = P_Customer_Item_Number;
SELECT MP.Organization_Id,
HROU.Date_From, HROU.Date_To
INTO Temp_Master_Organization_Id,
Temp_Date_From, Temp_Date_To
FROM HR_ORGANIZATION_UNITS HROU,
MTL_PARAMETERS MP
WHERE MP.Organization_Id =
P_Master_Organization_Id
AND HROU.Organization_Id =
MP.Organization_Id;
SELECT MP.Organization_Id, HROU.Date_From,
HROU.Date_To
INTO Temp_Master_Organization_Id, Temp_Date_From,
Temp_Date_To
FROM MTL_PARAMETERS MP,
HR_ORGANIZATION_UNITS HROU
WHERE MP.Organization_Code = P_Master_Organization_Code
AND HROU.Organization_Id = MP.Organization_id;
SELECT MP.Organization_Id, HROU.Date_From,
HROU.Date_To
INTO Temp_Master_Organization_Id, Temp_Date_From,
Temp_Date_To
FROM MTL_PARAMETERS MP,
HR_ORGANIZATION_INFORMATION HROI,
HR_ORGANIZATION_UNITS HROU
WHERE HROU.Name = P_Master_Organization_Name
AND MP.Organization_Id = HROU.Organization_Id
AND HROI.Organization_Id = MP.Organization_Id
AND HROI.Org_Information_Context = 'CLASS'
AND HROI.Org_Information1 = 'INV'
AND HROI.Org_Information2 = 'Y';
SELECT segment_num from
fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
order by segment_num;
SELECT Inventory_Item_Id
INTO Temp_Inventory_Item_Id
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.Inventory_Item_Id = P_Inventory_Item_Id
AND MSI.Organization_Id = P_Master_Organization_Id
AND NVL(MSI.Approval_Status,'A') = 'A';--Added for 11.5.10 PLM
SELECT ID_Flex_Num
INTO L_StructNum
FROM fnd_id_flex_structures
WHERE application_id = 401
AND id_flex_code = 'MSTK'
AND upper(enabled_flag) = 'Y';
SELECT ID_Flex_Num
INTO L_StructNum
FROM fnd_id_flex_structures
WHERE application_id = 401
AND id_flex_code = 'MSTK'
AND upper(enabled_flag) = 'Y';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT1';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT2';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT3';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT4';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT5';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT6';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT7';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT8';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT9';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT10';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT11';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT12';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT13';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT14';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT15';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT16';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT17';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT18';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT19';
select segment_num
into L_SegNumDummy
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MSTK'
and enabled_flag = 'Y'
and application_column_name = 'SEGMENT20';
SELECT Inventory_Item_Id
INTO Temp_Inventory_Item_Id
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.Inventory_Item_Id = P_Inventory_Item_Id
AND MSI.Organization_Id = P_Master_Organization_Id
AND NVL(MSI.Approval_Status,'A') = 'A';
P_Program_Update_Date IN Date DEFAULT NULL ) IS
BEGIN
IF ((P_Request_Id IS NOT NULL) AND (P_Program_Application_Id IS NOT NULL) AND (P_Program_Id IS NOT NULL)
AND (P_Program_Update_Date IS NOT NULL) AND (P_Program_Update_Date <= SYSDATE)) THEN
Manage_Error_Code('IN', 'APP-00000', Curr_Error);
SELECT Customer_Id, Customer_Item_Number, Item_Definition_Level, Customer_Category_Code, Address_Id
INTO Temp_Customer_Id, Temp_Customer_Item_Number, Temp_Item_Definition_Level, Temp_Customer_Category_Code, Temp_Address_Id
FROM MTL_CUSTOMER_ITEMS MCI
WHERE MCI.Customer_Id = P_Customer_Id
AND MCI.Customer_Item_Number = P_Customer_Item_Number
AND MCI.Item_Definition_Level = P_Item_Definition_Level
AND NVL(MCI.Customer_Category_Code, ' ') = NVL(P_Customer_Category_Code, ' ')
AND NVL(MCI.Address_Id, -1) = NVL(P_Address_Id, -1);
SELECT Customer_Item_Id, Inventory_Item_Id, Master_Organization_Id
INTO Temp_Customer_Item_Id, Temp_Inventory_Item_Id, Temp_Master_Organization_Id
FROM MTL_CUSTOMER_ITEM_XREFS MCIXRF
WHERE MCIXRF.Customer_Item_Id = P_Customer_Item_Id
AND MCIXRF.Inventory_Item_Id = P_Inventory_Item_Id
AND MCIXRF.Master_Organization_Id = P_Master_Organization_Id
AND Rownum = 1;
SELECT Customer_Item_Id, Master_Organization_Id, Preference_Number
INTO Temp_Customer_Item_Id, Temp_Master_Organization_Id, Temp_Preference_Number
FROM MTL_CUSTOMER_ITEM_XREFS MCIXRF
WHERE MCIXRF.Customer_Item_Id = P_Customer_Item_Id
AND MCIXRF.Master_Organization_Id = P_Master_Organization_Id
AND MCIXRF.Preference_Number = P_Preference_Number
AND Rownum = 1;
P_Last_Updated_By IN Number DEFAULT NULL,
P_Last_Update_Date IN Date DEFAULT NULL,
P_Created_By IN Number DEFAULT NULL,
P_Creation_Date IN Date DEFAULT NULL,
P_Customer_Item_Id IN Number DEFAULT NULL,
P_Inventory_Item_Id IN Number DEFAULT NULL,
P_Master_Organization_Id IN Number DEFAULT NULL,
P_Preference_Number IN Number DEFAULT NULL ) IS
BEGIN
IF (P_Origin = 'I') THEN
IF ((P_Customer_Id IS NULL) OR
(P_Customer_Item_Number IS NULL) OR
(P_Item_Definition_Level IS NULL) OR
((P_Item_Definition_Level = '2') AND (P_Customer_Category_Code IS NULL)) OR
((P_Item_Definition_Level = '3') AND (P_Address_Id IS NULL)) OR
(P_Inactive_Flag IS NULL) OR
(P_Last_Updated_By IS NULL) OR
(P_Last_Update_Date IS NULL) OR
(P_Created_By IS NULL) OR
(P_Creation_Date IS NULL)) THEN
FND_MESSAGE.Set_Name('INV', 'INV_REQUIRED_COLUMNS_MISSING');
(P_Last_Updated_By IS NULL) OR
(P_Last_Update_Date IS NULL) OR
(P_Created_By IS NULL) OR
(P_Creation_Date IS NULL)) THEN
FND_MESSAGE.Set_Name('INV', 'INV_REQUIRED_COLUMNS_MISSING');
PROCEDURE Insert_Row(
P_Origin IN Varchar2 DEFAULT NULL,
P_Last_Update_Date IN Date DEFAULT NULL,
P_Last_Updated_By IN Number DEFAULT NULL,
P_Creation_Date IN Date DEFAULT NULL,
P_Created_By IN Number DEFAULT NULL,
P_Last_Update_Login IN Number DEFAULT NULL,
P_Customer_Id IN Number DEFAULT NULL,
P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
P_Address_Id IN Number DEFAULT NULL,
P_Customer_Item_Number IN Varchar2 DEFAULT NULL,
P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
P_Customer_Item_Desc IN Varchar2 DEFAULT NULL,
P_Model_Customer_Item_Id IN Number DEFAULT NULL,
P_Commodity_Code_Id IN Number DEFAULT NULL,
P_Master_Container_Item_Id IN Number DEFAULT NULL,
P_Container_Item_Org_Id IN Number DEFAULT NULL,
P_Detail_Container_Item_Id IN Number DEFAULT NULL,
P_Min_Fill_Percentage IN Number DEFAULT NULL,
P_Dep_Plan_Required_Flag IN Varchar2 DEFAULT NULL,
P_Dep_Plan_Prior_Bld_Flag IN Varchar2 DEFAULT NULL,
P_Inactive_Flag IN Varchar2 DEFAULT NULL,
P_Attribute_Category IN Varchar2 DEFAULT NULL,
P_Attribute1 IN Varchar2 DEFAULT NULL,
P_Attribute2 IN Varchar2 DEFAULT NULL,
P_Attribute3 IN Varchar2 DEFAULT NULL,
P_Attribute4 IN Varchar2 DEFAULT NULL,
P_Attribute5 IN Varchar2 DEFAULT NULL,
P_Attribute6 IN Varchar2 DEFAULT NULL,
P_Attribute7 IN Varchar2 DEFAULT NULL,
P_Attribute8 IN Varchar2 DEFAULT NULL,
P_Attribute9 IN Varchar2 DEFAULT NULL,
P_Attribute10 IN Varchar2 DEFAULT NULL,
P_Attribute11 IN Varchar2 DEFAULT NULL,
P_Attribute12 IN Varchar2 DEFAULT NULL,
P_Attribute13 IN Varchar2 DEFAULT NULL,
P_Attribute14 IN Varchar2 DEFAULT NULL,
P_Attribute15 IN Varchar2 DEFAULT NULL,
P_Demand_Tolerance_Positive IN Number DEFAULT NULL,
P_Demand_Tolerance_Negative IN Number DEFAULT NULL,
P_Request_Id IN Number DEFAULT NULL,
P_Program_Application_Id IN Number DEFAULT NULL,
P_Program_Id IN Number DEFAULT NULL,
P_Program_Update_Date IN Date DEFAULT NULL,
P_Customer_Item_Id IN Number DEFAULT NULL,
P_Inventory_Item_Id IN Number DEFAULT NULL,
P_Master_Organization_Id IN Number DEFAULT NULL,
P_Preference_Number IN Number DEFAULT NULL
) IS
BEGIN
IF (P_Origin = 'I') THEN
INSERT
INTO MTL_CUSTOMER_ITEMS(
Customer_Item_Id,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Customer_Id,
Customer_Category_Code,
Address_Id,
Customer_Item_Number,
Item_Definition_Level,
Customer_Item_Desc,
Model_Customer_Item_Id,
Commodity_Code_Id,
Master_Container_Item_Id,
Container_Item_Org_Id,
Detail_Container_Item_Id,
Min_Fill_Percentage,
Dep_Plan_Required_Flag,
Dep_Plan_Prior_Bld_Flag,
Inactive_Flag,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Demand_Tolerance_Positive,
Demand_Tolerance_Negative,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date
)
VALUES
(
MTL_CUSTOMER_ITEMS_S.Nextval,
SYSDATE,
NVL(P_Last_Updated_By, -1),
SYSDATE,
NVL(P_Created_By, -1),
NVL(P_Last_Update_Login,-1),
P_Customer_Id,
P_Customer_Category_Code,
P_Address_Id,
P_Customer_Item_Number,
P_Item_Definition_Level,
trim(P_Customer_Item_Desc),
P_Model_Customer_Item_Id,
P_Commodity_Code_Id,
P_Master_Container_Item_Id,
P_Container_Item_Org_Id,
P_Detail_Container_Item_Id,
P_Min_Fill_Percentage,
P_Dep_Plan_Required_Flag,
P_Dep_Plan_Prior_Bld_Flag,
P_Inactive_Flag,
P_Attribute_Category,
P_Attribute1,
P_Attribute2,
P_Attribute3,
P_Attribute4,
P_Attribute5,
P_Attribute6,
P_Attribute7,
P_Attribute8,
P_Attribute9,
P_Attribute10,
P_Attribute11,
P_Attribute12,
P_Attribute13,
P_Attribute14,
P_Attribute15,
P_Demand_Tolerance_Positive,
P_Demand_Tolerance_Negative,
P_Request_Id,
P_Program_Application_Id,
P_Program_Id,
P_Program_Update_Date
);
INSERT
INTO MTL_CUSTOMER_ITEM_XREFS
(
Customer_Item_Id,
Inventory_Item_Id,
Master_Organization_Id,
Preference_Number,
Inactive_Flag,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date
)
VALUES
(
P_Customer_Item_Id,
P_Inventory_Item_Id,
P_Master_Organization_Id,
P_Preference_Number,
P_Inactive_Flag,
SYSDATE,
NVL(P_Last_Updated_By, -1),
SYSDATE,
NVL(P_Created_By, -1),
NVL(P_Last_Update_Login, -1),
P_Attribute_Category,
P_Attribute1,
P_Attribute2,
P_Attribute3,
P_Attribute4,
P_Attribute5,
P_Attribute6,
P_Attribute7,
P_Attribute8,
P_Attribute9,
P_Attribute10,
P_Attribute11,
P_Attribute12,
P_Attribute13,
P_Attribute14,
P_Attribute15,
P_Request_Id,
P_Program_Application_Id,
P_Program_Id,
P_Program_Update_Date
);
FND_MESSAGE.Set_Name('INV', 'INV_NO_ROW_INSERTED');
END Insert_Row;
PROCEDURE Delete_Row(
P_Origin IN Varchar2 DEFAULT NULL,
P_Delete_Record IN Varchar2 DEFAULT NULL,
P_Temp_RowId IN Varchar2 DEFAULT NULL
) IS
BEGIN
IF ((P_Origin = 'I') AND (UPPER(P_Delete_Record) = 'Y')) THEN
DELETE
FROM MTL_CI_INTERFACE
WHERE Rowid = P_Temp_RowId;
ELSIF ((P_Origin = 'X') AND (UPPER(P_Delete_Record) = 'Y')) THEN
DELETE
FROM MTL_CI_XREFS_INTERFACE
WHERE Rowid = P_Temp_RowId;
ELSIF ((P_Origin = 'I') AND (UPPER(P_Delete_Record) = 'N')) THEN
UPDATE MTL_CI_INTERFACE
SET Process_Mode = 3
WHERE Rowid = P_Temp_RowId;
ELSIF ((P_Origin = 'X') AND (UPPER(P_Delete_Record) = 'N')) THEN
UPDATE MTL_CI_XREFS_INTERFACE
SET Process_Mode = 3
WHERE Rowid = P_Temp_RowId;
FND_MESSAGE.Set_Name('INV', 'INV_NO_ROW_DELETED');
END Delete_Row;