Home » RDBMS Server » Performance Tuning » Please help on this (Oracle 11g)
| Please help on this [message #592293] |
Tue, 06 August 2013 06:37  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi All,
I have stuck up in implementing following scenarios.
Req1:
IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table
IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table
based on the below condition.
UPDATE LEAD_TIME_TABLE SET lead_time= lead time from xml message
WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;
Req3:
IF the action is add then the records should be inserted in the "LEAD_TIME_TABLE" and "LEAD_TIME_TABLE_STAGING" table
based on the below conditions.
Req3.1:
If SKU from XML message is available in "schema_name.SKU_TABLE" table.
The schema_name.SKU_TABLE table should be selected based on "business_unit_id" of XML message and "sid" of MAPPING table WHERE business_unit_id=sid.
then the three parameter(GEO SKU LEAD_TIME) values from XML message and two fields MODEL,CLASS from SKU_TABLE
should be inserted into "LEAD_TIME_TABLE" table.
Req3.2:
If SKU from XML message is not available in "schema_name.SKU_TABLE" table
The schema_name.SKU_TABLE table should be selected based on "business_unit_id" of XML message and "sid" of MAPPING table WHERE business_unit_id=sid.
then the four parameter(BUSINESS_UNIT_ID GEO SKU LEAD_TIME) values from XML message
should be inserted into "LEAD_TIME_TABLE_STAGING" table.
I am posting required scripts and test cases.
CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class VARCHAR2(20),geo VARCHAR2(6),lead_time NUMBER,created DATE);
CREATE TABLE LEAD_TIME_TABLE_STAGING(business_unit_id NUMBER,sku VARCHAR2(20),geo VARCHAR2(6),lead_time NUMBER);
CREATE TABLE SKU_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class VARCHAR2(10));
CREATE TABLE mapping(sid NUMBER,schema_name VARCHAR2(20));
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814817','GB',30);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AT',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814819','GB',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814820','BE',20);
INSERT INTO SKU_TABLE VALUES('A0814817','M125','C111');
INSERT INTO SKU_TABLE VALUES('A0814818','M127','A111');
INSERT INTO SKU_TABLE VALUES('A0814819','M126','A111');
INSERT INTO SKU_TABLE VALUES('A0814820','N100','B111');
INSERT INTO SKU_TABLE VALUES('A0814821','P123','B111');
INSERT INTO mapping VALUES(202,'WEDB');
INSERT INTO mapping VALUES(2828,'HR');
INSERT INTO mapping VALUES(3838,'BB');
Test case for req3.1:
The action for SKU numbers "A0814821" "A0814822" are insert.
The SKU number "A0814821" is available in schema_name.SKU_TABLE table.
so the record should be inserted in the LEAD_TIME_TABLE.
SELECT * FROM LEAD_TIME_TABLE;
SKU MODEL CLASS GEO LEAD_TIME
A0814817 M125 C111 GB 30
A0814818 M127 A111 AT 20
A0814819 M126 A111 GB 20
A0814820 N100 B111 BE 20
A0814821 P123 B111 GB 30
Test case for req3.2:
The action for SKU numbers "A0814821" "A0814822" are insert.
The SKU number "A0814822" is not available in SKU_TABLE table.
so the record should be inserted in the LEAD_TIME_TABLE_STAGING.
SELECT * FROM LEAD_TIME_TABLE_STAGING;
BUSINESS_UNIT_ID SKU GEO LEAD_TIME
2828 A0814822 BE 30
For requirement1 I have created the following package.
CREATE OR REPLACE PACKAGE BODY messg_values_process
AS
procedure ProcessInboundLtimeMessage(p_xml_message XmlType)
is
begin
for work_unit in (
select message.MESSAGE_ID
, work_unit.ACTION
, work_unit.LEADTIME_NODES
from XmlTable(
'/MESSAGES/MESSAGE'
passing p_xml_message
columns
MESSAGE_NO for ordinality
, MESSAGE_ID number path '@ID'
, WORK_UNIT_NODES XmlType path 'MSG/WORK_SET/WORK_UNIT'
) message
, XmlTable(
XmlNamespaces(
'urn:schemas-microsoft-com:rowset' as "rs"
, '#RowsetSchema' as "z"
),
'/WORK_UNIT'
passing message.WORK_UNIT_NODES
columns
WORK_UNIT_NO for ordinality
, ACTION varchar2(10 char) path '@ACTION'
, LEADTIME_NODES XmlType path 'RECORDSET[@TABLE_NAME="LEAD_TIME"]/xml/rs:data/z:row'
) work_unit
)
LOOP
CASE work_unit.ACTION
WHEN 'ADD' THEN MergeData(work_unit.LEADTIME_NODES);
WHEN 'UPDATE' THEN MergeData(work_unit.LEADTIME_NODES);
WHEN 'DELETE' THEN RemoveData(work_unit.LEADTIME_NODES);
ELSE null;
END CASE;
END LOOP;
END ProcessInboundLtimeMessage;
procedure UpdateData(p_leadtime_nodes XmlType)
is
begin
if (p_leadtime_nodes is not null) then
UPDATE ltc_global.LEAD_TIME_TABLE old SET lead_time=
(
select LEAD_TIME
from XmlTable(
XmlNamespaces(
'urn:schemas-microsoft-com:rowset' as "rs"
, '#RowsetSchema' as "z"
),
'/z:row[not(@business_unit_id=following-sibling::z:row/@business_unit_id)]'
passing p_leadtime_nodes
columns
ITEM_NO for ordinality
, BUSINESS_UNIT_ID number path '@business_unit_id'
, GEO varchar2(20 char) path '@geo'
, SKU varchar2(20 char) path '@sku'
, LEAD_TIME number path '@lead_time'
)new
WHERE new.SKU=old.sku
AND new.geo=old.geo
)
WHERE EXISTS
(
select *
from XmlTable(
XmlNamespaces(
'urn:schemas-microsoft-com:rowset' as "rs"
, '#RowsetSchema' as "z"
),
'/z:row[not(@business_unit_id=following-sibling::z:row/@business_unit_id)]'
passing p_leadtime_nodes
columns
ITEM_NO for ordinality
, BUSINESS_UNIT_ID number path '@business_unit_id'
, GEO varchar2(20 char) path '@geo'
, SKU varchar2(20 char) path '@sku'
, LEAD_TIME number path '@lead_time'
) new
WHERE new.SKU=old.sku
AND new.geo=old.geo
);
end if;
exception
when others then
LogDataError(
'UpdateData Procedure Failed #: '||chr(10)||sqlerrm
, null
, null
);
END UpdateData;
END messg_values_process;
/
some of the records are not updating in the table.
Test case:
<MESSAGES>
<MESSAGE ID="3026900">
<MSG_ID>3026900</MSG_ID>
<DT_POSTED>6/20/2013 08:15:48</DT_POSTED>
<POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>
<DT_LAST_QUEUED />
<MSG>
<WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">
<WORK_UNIT GROUP="LEAD_TIME" ACTION="UPDATE" AFFECTED="LEAD_TIME">
<RECORDSET TABLE_NAME="LEAD_TIME">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
<z:row business_unit_id="3838" geo="AT" sku="A0814818" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
<z:row business_unit_id="202" geo="GB" sku="A0814819" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
<z:row business_unit_id="2828" geo="BE" sku="A0814820" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
<z:row business_unit_id="2828" geo="BE" sku="A0814823" dw_maint_flag="False" stock_status_id="0" lead_time="40" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
</rs:data>
</xml>
</RECORDSET>
</WORK_UNIT>
<WORK_UNIT GROUP="LEAD_TIME" ACTION="ADD" AFFECTED="LEAD_TIME">
<RECORDSET TABLE_NAME="LEAD_TIME">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
<z:row business_unit_id="202" geo="GB" sku="A0814821" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
<z:row business_unit_id="2828" geo="BE" sku="A0814822" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
</rs:data>
</xml>
</RECORDSET>
</WORK_UNIT>
<WORK_UNIT GROUP="LEAD_TIME" ACTION="DELETE" AFFECTED="LEAD_TIME">
<RECORDSET TABLE_NAME="LEAD_TIME">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
<z:row business_unit_id="202" geo="GB" sku="A0814817" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
<z:row business_unit_id="2828" geo="BE" sku="A0814816" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
</rs:data>
</xml>
</RECORDSET>
</WORK_UNIT>
</WORK_SET>
</MSG>
</MESSAGE>
</MESSAGES>
CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class CHAR(1),geo VARCHAR2(6),lead_time NUMBER,created DATE);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AT',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AB',10);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814820','BE',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814819','GB',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814823','BE',20);
Why the following two records were not update.
Even satisfying this condition WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;
'A0814820','BE',20
'A0814823','BE',20
After updating the output I got as below.
VK_UNIT MODEL CLASS GEO LEAD_TIME CREATED
---------- --------- --- -------------------- --------------------------------------------
A0814818 AT 30
A0814818 AB 10
A0814820 BE 20
A0814819 GB 30
A0814823 BE
5 rows selected.
Please help me.
Your earliest response is appreciated.
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Please help on this [message #594562 is a reply to message #592321] |
Fri, 30 August 2013 02:54  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
You have requested that numerous posts be deleted from this thread, but you have not supplied a valid reason for your requests. This forum does not remove posts just because another poster requests it. Please tell the moderators WHY you want those posts removed.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 23 15:15:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
