Unable to use a record for update.

[pavans]

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.

[mahadev.v]

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?

[pavans]

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?

Yes, the record is already existing.
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.

[ray.wurlod]

The message is telling you that it is unable to INSERT.

Check your update action property.

[pavans]

The message is telling you that it is unable to INSERT.

Check your update action property. ...

Hi Ray,

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.

[pavans]

The message is telling you that it is unable to INSERT.

Check your update action property. ...

Hi Ray,

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.

I am able to view the data.
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;

[Sainath.Srinivasan]

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.

[pavans]

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.

Sorry my Mistake.

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.

[pavans]

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.

Sorry my Mistake.

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??

[chulett]

What do the jobs that do not run have in common? What is different between them and the ones that do work without error?

[pavans]

What do the jobs that do not run have in common? What is different between them and the ones that do work without error? ...

The jobs which are running and which are not running are:

Oracle Read
|
ORACLE Read -lookup-- Oracle Write

All the parameters in jobs are same.
The Params are:
DATABASE NAME
USERID
PWD
APT_CONFIG_FILE

[pavans]

What do the jobs that do not run have in common? What is different between them and the ones that do work without error? ...

The jobs which are running and which are not running are:

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'
;