Unable to use a record for update.
Moderators: chulett, rschirm, roy
Unable to use a record for update.
Hi,
We are currently on migrating Datastage jobs(7.5.1) from Sun Solaris to AIX box. We migrated few jobs and testing them.
We have a simple job with
Dataset--Transformer-OCI
I am not able to insert/update any record to the Database(oracle).
Error:
Unable to use a record for update.
Operator terminated abnormally: runLocally did not return APT_StatusOk.
Any Ideas.
We are currently on migrating Datastage jobs(7.5.1) from Sun Solaris to AIX box. We migrated few jobs and testing them.
We have a simple job with
Dataset--Transformer-OCI
I am not able to insert/update any record to the Database(oracle).
Error:
Unable to use a record for update.
Operator terminated abnormally: runLocally did not return APT_StatusOk.
Any Ideas.
Thanks,
Pavan
Pavan
Yes, the record is already existing.mahadev.v wrote:What happens when you try to insert the records? Did you check if there is a record in the table with the same keys as the update record?
We just update the updt_ts field with the latest date based on the key column which is item_i.
I tried for inserts and even that is not happeneing.
Oracle_Enterprise_135,0: Unable to insert a record into the table.
IS this something related to set-up on the AIX box.
Thanks,
Pavan
Pavan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,ray.wurlod wrote:The message is telling you that it is unable to INSERT.
Check your update action property. ...
Is it possible that, we are not able to insert or update a record to a Oracle Database on AIX box due to NLS setting.
My Oracle database NLS is US7ASCII.
What should be NLS setting in my jobs.
Do we need to do any setting on the ETL Server.
Thanks,
Pavan
Pavan
I am able to view the data.pavans wrote:Hi Ray,ray.wurlod wrote:The message is telling you that it is unable to INSERT.
Check your update action property. ...
Is it possible that, we are not able to insert or update a record to a Oracle Database on AIX box due to NLS setting.
My Oracle database NLS is US7ASCII.
What should be NLS setting in my jobs.
Do we need to do any setting on the ETL Server.
But when i run the job, its saying
ORCL_ReadFinItem: The provided query statement did not prepare correctly;
please verify that your statement is correct;
Thanks,
Pavan
Pavan
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Sorry my Mistake.Sainath.Srinivasan wrote:The name of the stage appear to imply a "Read" rather than "Write".
Is this different from your previous query?
Can you post the SQL.
Update Query is :
UPDATE
ITEM
SET
COST_A = ORCHESTRATE.COST_A
WHERE
(ITEM_I = ORCHESTRATE.ITEM_I).
The same works in 7.5.1/Sun Soloris Server/Oracle 10.2
Where as in 7.5.3/AIX Server/Oracle 10.2
The error I am getiing is :
OrclPO_W,1: Unable to insert a record into the table.
Unable to insert a record into the table.
After searching the forum i could find that:
The link:
ln -s $ORACLE_HOME/lib/libclntsh.so.10.1 libclntsh.so.10.1
That links must be created in the next path:
$DSHOME/lib
I wil request the admins to create the above link.
Hope it will work.
I will post the outcome.......once done.
Thanks,
Pavan
Pavan
pavans wrote:Sorry my Mistake.Sainath.Srinivasan wrote:The name of the stage appear to imply a "Read" rather than "Write".
Is this different from your previous query?
Can you post the SQL.
Update Query is :
UPDATE
ITEM
SET
COST_A = ORCHESTRATE.COST_A
WHERE
(ITEM_I = ORCHESTRATE.ITEM_I).
The same works in 7.5.1/Sun Soloris Server/Oracle 10.2
Where as in 7.5.3/AIX Server/Oracle 10.2
The error I am getiing is :
OrclPO_W,1: Unable to insert a record into the table.
Unable to insert a record into the table.
After searching the forum i could find that:
The link:
ln -s $ORACLE_HOME/lib/libclntsh.so.10.1 libclntsh.so.10.1
That links must be created in the next path:
$DSHOME/lib
I wil request the admins to create the above link.
Hope it will work.
I will post the outcome.......once done.
Sorry for the long Delay.
Still we are getting the below error.
Some of the jobs are running fine and some are not due to below error.
OrclFinDwInvMstrUpdate: The provided update statement did not prepare correctly;
please verify that your statement is correct;
statement: UPDATE
FIN_DW_INV_MSTR
SET
PGM_UPDT_TS = :PGM_UPDT_TS, UPDT_PGM_I = :UPDT_PGM_I
WHERE
(FIN_INV_I = :FIN_INV_I).
----
main_program: -nchar_cs option is deprecated.
-use_strings option is deprecated.
-nchar_cs option is deprecated.
-use_strings option is deprecated.
----------------------
What we did :
Applied a patch for 7.5.3 ODBC.
ORACLE_HOME parameter set at project level is pointing to new path.
Verified TNS entries.
I am able to connect to Database through AIX box.
Any ideas what the issue could be??
Thanks,
Pavan
Pavan
The jobs which are running and which are not running are:chulett wrote:What do the jobs that do not run have in common? What is different between them and the ones that do work without error? ...
Oracle Read
|
ORACLE Read -lookup-- Oracle Write
All the parameters in jobs are same.
The Params are:
DATABASE NAME
USERID
PWD
APT_CONFIG_FILE
Thanks,
Pavan
Pavan
pavans wrote:The jobs which are running and which are not running are:chulett wrote:What do the jobs that do not run have in common? What is different between them and the ones that do work without error? ...
Oracle Read
|
ORACLE Read -lookup-- Oracle Write
All the parameters in jobs are same.
The Params are:
DATABASE NAME
USERID
PWD
APT_CONFIG_FILE
I have copied the generated OSH, from AIX and SUN SOLARIS servers.
Please find it below:
Generated OSH on AIX Server:
#################################################################
#### STAGE: OrclRcptW
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_QUERY_ID],password="[&_ETL_QUERY_PW]"}'
-query 'SELECT
NVL(RTRIM(DOC_TYPE_T), \'\') DOC_TYPE_T,
ORD_I,
NVL(RTRIM(RECV_NODE_T), \'\') RECV_NODE_T,
A.RCPT_D,
RCPT_I,
STAT_T,
DVR_N,
RCPT_LINE_I,
PRIM_LINE_I,
ITEM_I,
RECV_Q,
NVL(RTRIM(DSPS_C), \'\') DSPS_C,
TOT_RECV_Q,
ORIG_CTRY_T,
SCND_SERL_1_T,
EPRS_C ,\'X\' as Dummy,
NVL(RTRIM(ORD_STAT_T) , 0) ORD_STAT_T,
ORD_Q
FROM
RCPT_W A,
(SELECT (TRUNC(PGM_UPDT_TS) - 1) AS RCPT_D FROM FIN_DW_JOB_STAT WHERE FIN_DW_PGM_I=\'EDWDONE\') B
WHERE ( DECODE(TRUNC(A.RCPT_D),NULL,TO_DATE(\'01-01-1900\',\'DD-MM-YYYY\'),TRUNC(A.RCPT_D)) <= B.RCPT_D)'
-server '[&_EDW_DATABASE]'
-use_strings
## General options
[ident('OrclRcptW'); jobmon_ident('OrclRcptW')]
## Outputs
0> [modify (
DOC_TYPE_T:nullable string[max=40]=DOC_TYPE_T;
ORD_I:nullable string[40]=ORD_I;
RECV_NODE_T:nullable string[max=40]=RECV_NODE_T;
RCPT_D:nullable timestamp=RCPT_D;
RCPT_I:not_nullable string[max=40]=RCPT_I;
STAT_T:nullable string[15]=STAT_T;
DVR_N:nullable string[max=50]=DVR_N;
RCPT_LINE_I:not_nullable decimal[5,0]=RCPT_LINE_I;
PRIM_LINE_I:nullable decimal[5,0]=PRIM_LINE_I;
ITEM_I:nullable string[40]=ITEM_I;
RECV_Q:nullable decimal[14,4]=RECV_Q;
DSPS_C:nullable string[max=20]=DSPS_C;
TOT_RECV_Q:nullable decimal[14,4]=TOT_RECV_Q;
ORIG_CTRY_T:nullable string[max=40]=ORIG_CTRY_T;
SCND_SERL_1_T:nullable string[max=40]=SCND_SERL_1_T;
EPRS_C:nullable string[24]=EPRS_C;
DUMMY:not_nullable string[max=1]=DUMMY;
ORD_STAT_T:nullable string[max=40]=ORD_STAT_T;
ORD_Q:nullable decimal[14,4]=ORD_Q;
keep
DOC_TYPE_T,ORD_I,RECV_NODE_T,RCPT_D,
RCPT_I,STAT_T,DVR_N,RCPT_LINE_I,
PRIM_LINE_I,ITEM_I,RECV_Q,DSPS_C,
TOT_RECV_Q,ORIG_CTRY_T,SCND_SERL_1_T,EPRS_C,
DUMMY,ORD_STAT_T,ORD_Q;
)] 'OrclRcptW:LkinToLkupFinInvI.v'
;
#################################################################
#### STAGE: OrclRcptXref
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_QUERY_ID],password="[&_ETL_QUERY_PW]"}'
-query 'SELECT
A.FIN_INV_I,A.RCPT_I,A.RCPT_LINE_I
FROM
RCPT_XREF A,RCPT_W B
WHERE A.RCPT_I=B.RCPT_I
And A.RCPT_LINE_I =B.RCPT_LINE_I '
-server '[&_EDW_DATABASE]'
-use_strings
## General options
[ident('OrclRcptXref'); jobmon_ident('OrclRcptXref')]
## Outputs
0> [modify (
FIN_INV_I:not_nullable int64=FIN_INV_I;
RCPT_I:not_nullable string[max=40]=RCPT_I;
RCPT_LINE_I:not_nullable decimal[5,0]=RCPT_LINE_I;
keep
FIN_INV_I,RCPT_I,RCPT_LINE_I;
)] 'OrclRcptXref:LkinToLkupFinInvI_Ref.v'
;
#################################################################
#### STAGE: LkupFinInvI
## Operator
lookup
## Operator options
-table
-key RCPT_I
-key RCPT_LINE_I
-table
-key DUMMY
-table
-key ITEM_I
-table
-key DUMMY
-ifNotFound continue
## General options
[ident('LkupFinInvI'); jobmon_ident('LkupFinInvI')]
## Inputs
0< 'OrclRcptW:LkinToLkupFinInvI.v'
1< 'OrclRcptXref:LkinToLkupFinInvI_Ref.v'
2< 'OrclFinDwInvMstr:LkinToLkpOrclFinDwInvMstr.v'
3< [view (
ITEM_I=MZRT_ITEM_I;
)] 'OrclFinItemI:LkinToLkupOrclFinItemI.v'
4< 'OrclFinDwJobStat:LkinToLkupOrclFinDwJobStat.v'
## Outputs
0> [modify (
FIN_INV_I_SEQ:nullable decimal[10,0]=FIN_INV_I_SEQ;
keep
DOC_TYPE_T,ORD_I,RECV_NODE_T,EPRS_C,
RCPT_D,RCPT_I,STAT_T,DVR_N,
RCPT_LINE_I,PRIM_LINE_I,ITEM_I,RECV_Q,
DSPS_C,TOT_RECV_Q,ORIG_CTRY_T,FIN_INV_I,
FIN_INV_I_SEQ,FIN_ITEM_I,ACCT_D,OWN_CO_I,
RETL_A,ORD_STAT_T,ORD_Q;)] 'LkupFinInvI:LkInToTrnsFinInv.v'
;
#################################################################
#### STAGE: OrclFinDwInvMstr
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_QUERY_ID],password="[&_ETL_QUERY_PW]"}'
-query 'SELECT NVL(MAX(FIN_INV_I),0) AS FIN_INV_I_SEQ, \'X\' AS DUMMY FROM FIN_DW_INV_MSTR'
-server '[&_EDW_DATABASE]'
-use_strings
## General options
[ident('OrclFinDwInvMstr'); jobmon_ident('OrclFinDwInvMstr')]
## Outputs
0> [modify (
FIN_INV_I_SEQ:not_nullable decimal[10,0]=FIN_INV_I_SEQ;
DUMMY:not_nullable string[max=1]=DUMMY;
keep
FIN_INV_I_SEQ,DUMMY;
)] 'OrclFinDwInvMstr:LkinToLkpOrclFinDwInvMstr.v'
;
#### STAGE: TrnsFinInv.LkInToTrnsFinInv_Part
## Operator
modulus -key FIN_INV_I
## General options
[ident('TrnsFinInv.LkInToTrnsFinInv_Part')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv.v'
## Outputs
0> [] 'LkupFinInvI:LkInToTrnsFinInv_Part.v'
;
#### STAGE: TrnsFinInv.LkInToTrnsFinInv_Sort
## Operator
tsort -key FIN_INV_I -asc -nonStable
## General options
[ident('TrnsFinInv.LkInToTrnsFinInv_Sort')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv_Part.v'
## Outputs
0> [] 'LkupFinInvI:LkInToTrnsFinInv_Part_Sort.v'
;
#################################################################
#### STAGE: TrnsFinInv
## Operator
transform
## Operator options
-flag run
-name 'V0S19_ecf2222a_TrnsFinInv'
## General options
[ident('TrnsFinInv'); jobmon_ident('TrnsFinInv')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv_Part_Sort.v'
## Outputs
0> [] 'TrnsFinInv:LkoutToOrclFinDwInvMstrUpdate.v'
1> [] 'TrnsFinInv:LkoutToDsetReceiptJoba.v'
2> [] 'TrnsFinInv:LkoutToDsetRcptXref.v'
;
#################################################################
#### STAGE: OrclFiinDwInvMstrInsert
## Operator
oraupsert
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_UPDATE_ID],password="[&_ETL_UPDATE_PW]"}'
-insert 'INSERT
INTO
FIN_DW_INV_MSTR
(FIN_INV_I, DATA_SRC_I, CREATE_TS, PGM_UPDT_TS, UPDT_PGM_I, UPDT_PGM_STAT_C, PRCS_PEND_F)
VALUES
(ORCHESTRATE.FIN_INV_I, ORCHESTRATE.DATA_SRC_I, ORCHESTRATE.CREATE_TS, ORCHESTRATE.PGM_UPDT_TS, ORCHESTRATE.UPDT_PGM_I,
ORCHESTRATE.UPDT_PGM_STAT_C, ORCHESTRATE.PRCS_PEND_F)'
-update 'UPDATE
FIN_DW_INV_MSTR
SET
DATA_SRC_I = ORCHESTRATE.DATA_SRC_I, CREATE_TS = ORCHESTRATE.CREATE_TS, PGM_UPDT_TS = ORCHESTRATE.PGM_UPDT_TS, UPDT_PGM_I =
ORCHESTRATE.UPDT_PGM_I, UPDT_PGM_STAT_C = ORCHESTRATE.UPDT_PGM_STAT_C, PRCS_PEND_F = ORCHESTRATE.PRCS_PEND_F
WHERE
(FIN_INV_I = ORCHESTRATE.FIN_INV_I)'
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclFiinDwInvMstrInsert'); jobmon_ident('OrclFiinDwInvMstrInsert')]
## Inputs
0< 'OrclFinDwInvMstrUpdate:LkoutToOrclFinDwInvMstrInsert.v'
;
#################################################################
#### STAGE: OrclFinDwInvMstrUpdate
## Operator
oraupsert
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_UPDATE_ID],password="[&_ETL_UPDATE_PW]"}'
-server '[&_EDW_DATABASE]'
-update 'UPDATE
FIN_DW_INV_MSTR
SET
PGM_UPDT_TS = ORCHESTRATE.PGM_UPDT_TS, UPDT_PGM_I = ORCHESTRATE.UPDT_PGM_I
WHERE
(FIN_INV_I = ORCHESTRATE.FIN_INV_I)'
-reject
## General options
[ident('OrclFinDwInvMstrUpdate'); jobmon_ident('OrclFinDwInvMstrUpdate')]
## Inputs
0< 'TrnsFinInv:LkoutToOrclFinDwInvMstrUpdate.v'
## Outputs
0> [] 'OrclFinDwInvMstrUpdate:LkoutToOrclFinDwInvMstrInsert.v'
;
#################################################################
#### STAGE: DsetReceiptJoba
## Operator
copy
## General options
[ident('DsetReceiptJoba')]
## Inputs
0< 'TrnsFinInv:LkoutToDsetReceiptJoba.v'
## Outputs
0>| [ds] '[&_DATA_WRK_DIR]/ecf2222aReceipt.ds'
;
#################################################################
#### STAGE: OrclFinItemI
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_QUERY_ID],password="[&_ETL_QUERY_PW]"}'
-query 'SELECT
A.FIN_ITEM_I, A.MZRT_ITEM_I, C.OWN_CO_I, C.RETL_A
FROM
MZRT_ITEM_XREF A,RCPT_W B,FIN_ITEM C
WHERE A.MZRT_ITEM_I=B.ITEM_I
And A.FIN_ITEM_I=C.FIN_ITEM_I'
-server '[&_EDW_DATABASE]'
-use_strings
## General options
[ident('OrclFinItemI'); jobmon_ident('OrclFinItemI')]
## Outputs
0> [modify (
FIN_ITEM_I:not_nullable decimal[10,0]=FIN_ITEM_I;
MZRT_ITEM_I:nullable string[12]=MZRT_ITEM_I;
OWN_CO_I:nullable int64=OWN_CO_I;
RETL_A:nullable decimal[11,2]=RETL_A;
keep
FIN_ITEM_I,MZRT_ITEM_I,OWN_CO_I,RETL_A;
)] 'OrclFinItemI:LkinToLkupOrclFinItemI.v'
;
#################################################################
#### STAGE: DsetRcptXref
## Operator
copy
## General options
[ident('DsetRcptXref')]
## Inputs
0< 'TrnsFinInv:LkoutToDsetRcptXref.v'
## Outputs
0>| [ds] '[&_DATA_WRK_DIR]/ecf2222aRcptXref.ds'
;
#################################################################
#### STAGE: OrclFinDwJobStat
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=[&_ETL_QUERY_ID],password="[&_ETL_QUERY_PW]"}'
-query 'SELECT
TRUNC(PGM_UPDT_TS) - 1 as ACCT_D
, \'X\' as DUMMY
FROM FIN_DW_JOB_STAT
WHERE FIN_DW_PGM_I = \'EDWDONE\''
-server '[&_EDW_DATABASE]'
-use_strings
## General options
[ident('OrclFinDwJobStat'); jobmon_ident('OrclFinDwJobStat')]
## Outputs
0> [modify (
ACCT_D:not_nullable timestamp=ACCT_D;
DUMMY:not_nullable string[max=1]=DUMMY;
keep
ACCT_D,DUMMY;
)] 'OrclFinDwJobStat:LkinToLkupOrclFinDwJobStat.v'
;
Generated OSH on Sun-Solaris Server:
#################################################################
#### STAGE: OrclRcptW
## Operator
oraread
## Operator options
-dboptions '{user=[&_ETL_QUERY_ID],password=[&_ETL_QUERY_PW]}'
-query 'SELECT
NVL(RTRIM(DOC_TYPE_T), \'\') DOC_TYPE_T,
ORD_I,
NVL(RTRIM(RECV_NODE_T), \'\') RECV_NODE_T,
A.RCPT_D,
RCPT_I,
STAT_T,
DVR_N,
RCPT_LINE_I,
PRIM_LINE_I,
ITEM_I,
RECV_Q,
NVL(RTRIM(DSPS_C), \'\') DSPS_C,
TOT_RECV_Q,
ORIG_CTRY_T,
SCND_SERL_1_T,
EPRS_C ,\'X\' as Dummy,
NVL(RTRIM(ORD_STAT_T) , 0) ORD_STAT_T,
ORD_Q
FROM
RCPT_W A,
(SELECT (TRUNC(PGM_UPDT_TS) - 1) AS RCPT_D FROM FIN_DW_JOB_STAT WHERE FIN_DW_PGM_I=\'EDWDONE\') B
WHERE ( DECODE(TRUNC(A.RCPT_D),NULL,TO_DATE(\'01-01-1900\',\'DD-MM-YYYY\'),TRUNC(A.RCPT_D)) <= B.RCPT_D)'
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclRcptW'); jobmon_ident('OrclRcptW')]
## Outputs
0> [modify (
DOC_TYPE_T:nullable string[max=40]=DOC_TYPE_T;
ORD_I:nullable string[40]=ORD_I;
RECV_NODE_T:nullable string[max=40]=RECV_NODE_T;
RCPT_D:nullable timestamp=RCPT_D;
RCPT_I:not_nullable string[max=40]=RCPT_I;
STAT_T:nullable string[15]=STAT_T;
DVR_N:nullable string[max=50]=DVR_N;
RCPT_LINE_I:not_nullable decimal[5,0]=RCPT_LINE_I;
PRIM_LINE_I:nullable decimal[5,0]=PRIM_LINE_I;
ITEM_I:nullable string[40]=ITEM_I;
RECV_Q:nullable decimal[14,4]=RECV_Q;
DSPS_C:nullable string[max=20]=DSPS_C;
TOT_RECV_Q:nullable decimal[14,4]=TOT_RECV_Q;
ORIG_CTRY_T:nullable string[max=40]=ORIG_CTRY_T;
SCND_SERL_1_T:nullable string[max=40]=SCND_SERL_1_T;
EPRS_C:nullable string[24]=EPRS_C;
DUMMY:not_nullable string[max=1]=DUMMY;
ORD_STAT_T:nullable string[max=40]=ORD_STAT_T;
ORD_Q:nullable decimal[14,4]=ORD_Q;
keep
DOC_TYPE_T,ORD_I,RECV_NODE_T,RCPT_D,
RCPT_I,STAT_T,DVR_N,RCPT_LINE_I,
PRIM_LINE_I,ITEM_I,RECV_Q,DSPS_C,
TOT_RECV_Q,ORIG_CTRY_T,SCND_SERL_1_T,EPRS_C,
DUMMY,ORD_STAT_T,ORD_Q;
)] 'OrclRcptW:LkinToLkupFinInvI.v'
;
#################################################################
#### STAGE: OrclRcptXref
## Operator
oraread
## Operator options
-dboptions '{user=[&_ETL_QUERY_ID],password=[&_ETL_QUERY_PW]}'
-query 'SELECT
A.FIN_INV_I,A.RCPT_I,A.RCPT_LINE_I
FROM
RCPT_XREF A,RCPT_W B
WHERE A.RCPT_I=B.RCPT_I
And A.RCPT_LINE_I =B.RCPT_LINE_I '
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclRcptXref'); jobmon_ident('OrclRcptXref')]
## Outputs
0> [modify (
FIN_INV_I:not_nullable int64=FIN_INV_I;
RCPT_I:not_nullable string[max=40]=RCPT_I;
RCPT_LINE_I:not_nullable decimal[5,0]=RCPT_LINE_I;
keep
FIN_INV_I,RCPT_I,RCPT_LINE_I;
)] 'OrclRcptXref:LkinToLkupFinInvI_Ref.v'
;
#################################################################
#### STAGE: LkupFinInvI
## Operator
lookup
## Operator options
-table
-key RCPT_I
-key RCPT_LINE_I
-table
-key DUMMY
-table
-key ITEM_I
-table
-key DUMMY
-ifNotFound continue
## General options
[ident('LkupFinInvI'); jobmon_ident('LkupFinInvI')]
## Inputs
0< 'OrclRcptW:LkinToLkupFinInvI.v'
1< 'OrclRcptXref:LkinToLkupFinInvI_Ref.v'
2< 'OrclFinDwInvMstr:LkinToLkpOrclFinDwInvMstr.v'
3< [view (
ITEM_I=MZRT_ITEM_I;
)] 'OrclFinItemI:LkinToLkupOrclFinItemI.v'
4< 'OrclFinDwJobStat:LkinToLkupOrclFinDwJobStat.v'
## Outputs
0> [modify (
FIN_INV_I_SEQ:nullable decimal[10,0]=FIN_INV_I_SEQ;
keep
DOC_TYPE_T,ORD_I,RECV_NODE_T,EPRS_C,
RCPT_D,RCPT_I,STAT_T,DVR_N,
RCPT_LINE_I,PRIM_LINE_I,ITEM_I,RECV_Q,
DSPS_C,TOT_RECV_Q,ORIG_CTRY_T,FIN_INV_I,
FIN_INV_I_SEQ,FIN_ITEM_I,ACCT_D,OWN_CO_I,
RETL_A,ORD_STAT_T,ORD_Q;)] 'LkupFinInvI:LkInToTrnsFinInv.v'
;
#################################################################
#### STAGE: OrclFinDwInvMstr
## Operator
oraread
## Operator options
-dboptions '{user=[&_ETL_QUERY_ID],password=[&_ETL_QUERY_PW]}'
-query 'SELECT NVL(MAX(FIN_INV_I),0) AS FIN_INV_I_SEQ, \'X\' AS DUMMY FROM FIN_DW_INV_MSTR'
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclFinDwInvMstr'); jobmon_ident('OrclFinDwInvMstr')]
## Outputs
0> [modify (
FIN_INV_I_SEQ:not_nullable decimal[10,0]=FIN_INV_I_SEQ;
DUMMY:not_nullable string[max=1]=DUMMY;
keep
FIN_INV_I_SEQ,DUMMY;
)] 'OrclFinDwInvMstr:LkinToLkpOrclFinDwInvMstr.v'
;
#### STAGE: TrnsFinInv.LkInToTrnsFinInv_Part
## Operator
modulus -key FIN_INV_I
## General options
[ident('TrnsFinInv.LkInToTrnsFinInv_Part')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv.v'
## Outputs
0> [] 'LkupFinInvI:LkInToTrnsFinInv_Part.v'
;
#### STAGE: TrnsFinInv.LkInToTrnsFinInv_Sort
## Operator
tsort -key FIN_INV_I -asc -nonStable
## General options
[ident('TrnsFinInv.LkInToTrnsFinInv_Sort')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv_Part.v'
## Outputs
0> [] 'LkupFinInvI:LkInToTrnsFinInv_Part_Sort.v'
;
#################################################################
#### STAGE: TrnsFinInv
## Operator
transform
## Operator options
-flag run
-name 'V0S19_ecf2222a_TrnsFinInv'
## General options
[ident('TrnsFinInv'); jobmon_ident('TrnsFinInv')]
## Inputs
0< 'LkupFinInvI:LkInToTrnsFinInv_Part_Sort.v'
## Outputs
0> [] 'TrnsFinInv:LkoutToOrclFinDwInvMstrUpdate.v'
1> [] 'TrnsFinInv:LkoutToDsetReceiptJoba.v'
2> [] 'TrnsFinInv:LkoutToDsetRcptXref.v'
;
#################################################################
#### STAGE: OrclFiinDwInvMstrInsert
## Operator
oraupsert
## Operator options
-dboptions '{user=[&_ETL_UPDATE_ID],password=[&_ETL_UPDATE_PW]}'
-insert 'INSERT
INTO
FIN_DW_INV_MSTR
(FIN_INV_I, DATA_SRC_I, CREATE_TS, PGM_UPDT_TS, UPDT_PGM_I, UPDT_PGM_STAT_C, PRCS_PEND_F)
VALUES
(ORCHESTRATE.FIN_INV_I, ORCHESTRATE.DATA_SRC_I, ORCHESTRATE.CREATE_TS, ORCHESTRATE.PGM_UPDT_TS, ORCHESTRATE.UPDT_PGM_I, ORCHESTRATE.UPDT_PGM_STAT_C, ORCHESTRATE.PRCS_PEND_F)'
-update 'UPDATE
FIN_DW_INV_MSTR
SET
DATA_SRC_I = ORCHESTRATE.DATA_SRC_I, CREATE_TS = ORCHESTRATE.CREATE_TS, PGM_UPDT_TS = ORCHESTRATE.PGM_UPDT_TS, UPDT_PGM_I = ORCHESTRATE.UPDT_PGM_I, UPDT_PGM_STAT_C = ORCHESTRATE.UPDT_PGM_STAT_C, PRCS_PEND_F = ORCHESTRATE.PRCS_PEND_F
WHERE
(FIN_INV_I = ORCHESTRATE.FIN_INV_I)'
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclFiinDwInvMstrInsert'); jobmon_ident('OrclFiinDwInvMstrInsert')]
## Inputs
0< 'OrclFinDwInvMstrUpdate:LkoutToOrclFinDwInvMstrInsert.v'
;
#################################################################
#### STAGE: OrclFinDwInvMstrUpdate
## Operator
oraupsert
## Operator options
-dboptions '{user=[&_ETL_UPDATE_ID],password=[&_ETL_UPDATE_PW]}'
-server '[&_EDW_DATABASE]'
-update 'UPDATE
FIN_DW_INV_MSTR
SET
PGM_UPDT_TS = ORCHESTRATE.PGM_UPDT_TS, UPDT_PGM_I = ORCHESTRATE.UPDT_PGM_I
WHERE
(FIN_INV_I = ORCHESTRATE.FIN_INV_I)'
-reject
## General options
[ident('OrclFinDwInvMstrUpdate'); jobmon_ident('OrclFinDwInvMstrUpdate')]
## Inputs
0< 'TrnsFinInv:LkoutToOrclFinDwInvMstrUpdate.v'
## Outputs
0> [] 'OrclFinDwInvMstrUpdate:LkoutToOrclFinDwInvMstrInsert.v'
;
#################################################################
#### STAGE: DsetReceiptJoba
## Operator
copy
## General options
[ident('DsetReceiptJoba')]
## Inputs
0< 'TrnsFinInv:LkoutToDsetReceiptJoba.v'
## Outputs
0>| [ds] '[&_DATA_WRK_DIR]/ecf2222aReceipt.ds'
;
#################################################################
#### STAGE: OrclFinItemI
## Operator
oraread
## Operator options
-dboptions '{user=[&_ETL_QUERY_ID],password=[&_ETL_QUERY_PW]}'
-query 'SELECT
A.FIN_ITEM_I, A.MZRT_ITEM_I, C.OWN_CO_I, C.RETL_A
FROM
MZRT_ITEM_XREF A,RCPT_W B,FIN_ITEM C
WHERE A.MZRT_ITEM_I=B.ITEM_I
And A.FIN_ITEM_I=C.FIN_ITEM_I'
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclFinItemI'); jobmon_ident('OrclFinItemI')]
## Outputs
0> [modify (
FIN_ITEM_I:not_nullable decimal[10,0]=FIN_ITEM_I;
MZRT_ITEM_I:nullable string[12]=MZRT_ITEM_I;
OWN_CO_I:nullable int64=OWN_CO_I;
RETL_A:nullable decimal[11,2]=RETL_A;
keep
FIN_ITEM_I,MZRT_ITEM_I,OWN_CO_I,RETL_A;
)] 'OrclFinItemI:LkinToLkupOrclFinItemI.v'
;
#################################################################
#### STAGE: DsetRcptXref
## Operator
copy
## General options
[ident('DsetRcptXref')]
## Inputs
0< 'TrnsFinInv:LkoutToDsetRcptXref.v'
## Outputs
0>| [ds] '[&_DATA_WRK_DIR]/ecf2222aRcptXref.ds'
;
#################################################################
#### STAGE: OrclFinDwJobStat
## Operator
oraread
## Operator options
-dboptions '{user=[&_ETL_QUERY_ID],password=[&_ETL_QUERY_PW]}'
-query 'SELECT
TRUNC(PGM_UPDT_TS) - 1 as ACCT_D
, \'X\' as DUMMY
FROM FIN_DW_JOB_STAT
WHERE FIN_DW_PGM_I = \'EDWDONE\''
-server '[&_EDW_DATABASE]'
## General options
[ident('OrclFinDwJobStat'); jobmon_ident('OrclFinDwJobStat')]
## Outputs
0> [modify (
ACCT_D:not_nullable timestamp=ACCT_D;
DUMMY:not_nullable string[max=1]=DUMMY;
keep
ACCT_D,DUMMY;
)] 'OrclFinDwJobStat:LkinToLkupOrclFinDwJobStat.v'
;
Thanks,
Pavan
Pavan