Open User Creation Form , Querry any one of the existing Username.Then Go to Help => Digonostics => Custom Code => Personalize
Create one function as below : =========================
seq: 1 description : Avoid Specila Characters in USERNAME.
Create Condition : =============
Trigger Event :WHEN-NEW-ITEM-INSTANCE Trigger Object :USER.USER_PASSWORD Condition : :USER.USER_NAME like ('%#%') or :USER.USER_NAME like ('%@%') or :USER.USER_NAME like ('%!%') or :USER.USER_NAME like ('%$%') or :USER.USER_NAME like ('%^%') or :USER.USER_NAME like ('%&%') or :USER.USER_NAME like ('%%%') or :USER.USER_NAME like ('%*%') and ${item.user.user_password.navigable.current}='TRUE'
Processing Mode : Both.
Now Create Actions For the True Condition : ====================================== seq : 1 Type : Builtin Language : All Built Type : GO_ITEM Argument : USER.USER_NAME
seq: 2 Type :Message Language : All Message Type : Error Message Text : Username Should not Contain Special Characters like !,@,#,$,%,^,&,*.
Save. Open New User Creation Form and try to create user with special characters. It Throws Error : Username Should not Contain Special Characters like !,@,#,$,%,^,&,*. when cursor is navigated to password field after entering the username.
When Invoice Booked and Saved ============================= One row created in ap_invoices_all and its distribution lines created in ap_invoice_distributions_all
When Invoice Validated : ====================== ap_invoice_distributions_all.MATCH_STATUS_FLAG='A' ap_invoice_distributions_all.ACCOUNTING_EVENT_ID=NOT NULL(Here 1370092) one row created in ap_accounting_events_all with accounting_event_id=ap_invoice_distributions_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='CREATED' ap_accounting_events_all.SOURCE_TABLE='AP_INVOICES' ap_accounting_events_all.SOURCE_ID=ap_invoice_distributions_all.INVOICE_ID= AP_INVOICE_ALL.INVOICE_ID
When Invoice Accounted : ===================== ap_invoice_distributions_all.ACCRUAL_POSTED_FLAG='Y' ap_invoice_distributions_all.POSTED_FLAG='Y' ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED' ONE ROW CREATED IN AP_AE_HEADERS_ALL where AP_AE_HEADERS_ALL. accounting_event_id=ap_accounting_events_all.accounting_event_id Rows created in ap_ae_lines_all where AP_AE_HEADERS_ALL.ae_header_id=ap_ae_lines_all.ae_header_id as below The number of rows generally created in ap_ae_lines_all counted as 1) one row for invoice with ap_ae_lines_all.AE_LINE_TYPE_CODE='LIABILITY' ap_ae_lines_all.SOURCE_TABLE='AP_INVOICES' ,ap_ae_lines_all.source_id=ap_invoices_all.invoice_id2) Other rows are created for the invoice distribution lines (one line per invoice distribution line).ap_ae_lines_all.AE_LINE_TYPE_CODE='CHARGE', SOURCE_TABLE='AP_INVOICE_DISTRIBUTIONS', ap_ae_lines_all.SOURCE_ID=AP_INVOICE_DISTRIBUTIONS.INVOICE_ID
When Invoice Approved : ======================== ap_invoices_all.WFAPPROVAL_STATUS='MANUALLY APPROVED', initially it was 'REQUIRED'
When Payment Created ===================== when payment created the one record created in ap_checks_all table.
When Payment Accounted ============================= When payment document accounted then one row is created in ap_accounting_events_all table. AP_invoice_payments_all.ACCOUNTING_EVENT_ID= ap_accounting_events_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'. andap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id.
After Doing the Payment (paid) of invoice with Created Payment Document ============================================================= ap_invoices_all.payment_status_flag='Y' BEFORE 'N'It creates the linKing between ap_invoices_all and ap_checks_all by AP_INVOICE_PAYMENTS_ALL.one row created in AP_INVOICE_PAYMENTS_ALL with reference of invoice id. AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG='Y' AP_INVOICE_PAYMENTS_ALL.CASH_POSTED_FLAG='Y' AP_INVOICE_PAYMENTS_ALL.POSTED_FLAG='Y'and when get void the AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG='Y' unless it is 'N' When payment got accounted the one row created in ap_accounting_events_all with ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'
After Clearing Check from cash management ===================================== open payment document and create accounting for it, showing partial now. after successfull accounting of the document: one line is created in AP_PAYMENT_HISTORY_all with new accounting _event_id. AP_PAYMENT_HISTORY_all.accounting_event_id= ap_accounting_events_all.accounting_event_idone new line created in ap_accounting_events_all with EVENT_TYPE_CODE='PAYMENT CLEARING'and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS' ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id andAP_PAYMENT_HISTORY_all.accounting_event_id= ap_accounting_events_all.accounting_event_id one row created in AP_AE_HEADERS_ALL with new accounting_event_id and two rows in this case created in ap_ae_lines_all with AE_LINE_TYPE_CODE='CASH CLEARING ' AND 'CASH',SOURCE_TABLE='AP_CHECKS'.
When Posted in GL (GL_POSTING) =============================== after running the request "Payables Transfer to General Ledger".The ap_ae_lines_all.GL_SL_LINK_ID populates.AP_AE_HEADERS_ALL.GL_TRANSFER_FLAG='Y'AP_AE_HEADERS_ALL. GL_TRANSFER_RUN_ID IS NOT NULL AP_AE_HEADERS_ALL.TRIAL_BALANCE_FLAG='Y'
What are the various types of reports ? * Tabular, Master Detail, Form, Form Letter, Mailing Labels, Matrix
2. What is the difference between Master - Detail Report and report created by breaks ? * Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.
3. What are Anchors ? * An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.
4. What are the various types of anchors in Reports ? A There are two types of anchors in Oracle Reports: * implicit (anchors that Oracle Reports creates when a report is run) * explicit (anchors you create)
Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.
Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog
9. What are the various report triggers ? What is their order of firing ? A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order : * Before Parameter Form * After Parameter Form * Before Report * Between Pages * After Report
Apart from the above Five Report Triggers, there are three other types of triggers : * Validation Triggers * Format Triggers * Action Triggers
Before Form : Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).
After Form : Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).
Before Report : Fires before the report is executed but after queries are parsed and data is fetched.
Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)
After Report : Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.
Validation Triggers : Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.
Format Triggers : Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.
Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.
19. What are Placeholder Columns ? A A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).
20. What are the various Module Types in Reports ? A You can build three types of modules with Oracle Reports: * external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules * external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules * reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules
22. What are Physical and Logical pages in Reports ? A A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.
Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.
Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.
23. What are the various page layout sections in Oracle Reports ? A A report has three sections : the report header pages, report body/margin pages, and report trailer pages.
27. What are various types of parameters ? A There are two types of parameters: * default (called system parameters) * user-created (called bind and lexical parameters)
30. How do you reference parameters and columns in reports ? A In two ways : * As bind references * As lexical references
31. What are Bind Referencing and Lexical Referencing ? * Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.
Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
33. Can you create a group without any break columns ? * No.
35. Types of Matrix report ? * 1. Single query 2. Multi query 3. Nested Query 4. Matrix Break
37. In Reports, how can you print one record per page in the output ? * Set the Maximum records per page property of the Repeating frame to 1.
38. How do you print a Report 2.5 report in character mode ? * Set MODE = 'Character' in the Parameter form
39. What are widow lines ? * Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.
40. What are widow records ? * Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.
41. What is 'page protect' property for objects ? * Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.
42. What is the 'Print Condition Type' property ? * 'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.
44. What are the various values of the 'Print Condition Type' property in Reports ? * The various values are : All : All means the object and all of its contents will be printed on all logical pages of the Print Condition Object. The object will be repeated on any overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.
All but First : All but First means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the first logical page. The object will be formatted only on overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.
All but Last : All but Last means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the last logical page. The object will be repeated on any overflow pages of the Print Condition Object except the last one and will be truncated at the logical page boundary, if necessary.
*Default : Default means that Oracle Reports will use object positioning to set the Print Condition Type to either *First or *Last for you. (The asterisk indicates that Oracle Reports specified the setting for you.)
First : First means that the object and all of its contents will only be printed on the first logical page of the Print Condition Object. The object will be formatted and will overflow to subsequent pages, if necessary.
Last : Last means that the object and all of its contents will only be printed on the last logical page of the Print Condition Object. The object will be formatted after the Print Condition Object and will overflow to subsequent pages, if necessary.
* For repeating frames, the print condition type property refers to every logical page of every instance (record) of the repeating frame. If you specify a Print Condition Type setting of All and a Print Condition Object setting of Enclosing Object for a field F_1, it prints in every instance of repeating frame R_1 on every logical page. If you specify a Print Condition Type setting of All but First in the above case , then the field will be printed only in those cases where a single instance (row or record) of the repeating frame spans across more than one page (which is normally not the case since many records are there in a single logical page and not one record in many pages). In such a case the field will be printed on all subsequent pages of this instance except the first page.
43. What is the 'Print Condition Object' property ? * 'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.
45. What are the various values of the 'Print Condition Object' property in Reports ? * The various values are : Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored. Enclosing Object : Enclosing Object is the object that encloses the current object.
46. What is the horizontal of vertical sizing property of objects ? * Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.
47. What are the various values of the horizontal of vertical sizing property ? * The various values are : Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")
Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")
Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.
Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.
51. How do you display a message in reports ? * SRW.Message
54. What are the various values of 'Print Panel Order' property of report ? * The various values are : Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.
Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.
55. What is the 'Print Direction' Property of Repeating frames ? * 'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.
56. What are the various values of the 'Print Direction' Property of Repeating frames ? * The various values are : Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.
Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.
Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.
Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.
59. What is the 'Keep with Anchoring Object' object property ? * 'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.
60. What is 'Page Break Before' object property ? * 'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.
61. What is 'Page Break After' object property ? * 'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.
63. What is the 'Break Order' property of columns ? * The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.
64. What are the various types of links ? * The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).
65. Name some of the procedures in the SRW package ? * SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report
66. What are the various report layout regions ? * There are three report regions in the Layout editor : * header * body/margin * trailer
Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.
Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations. A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.
Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.
70. What is more efficient : Maximum rows or Group Filter ? * Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.
Includes two scripts to fetch all the transactions information related with in a procure to pay cycle.
Two scripts are provided to use one with receipts and other when receipts are not created. Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL Transfer flag e.t.c
WITH OUT RECEIPTS
=========================== -- WITH OUT RECEIPTS -- procure to pay cycle query select distinct reqh.segment1 REQ_NUM, reqh.AUTHORIZATION_STATUS REQ_STATUS, -- poh.po_header_id, poh.segment1 PO_NUM, pol.line_num, poh.AUTHORIZATION_STATUS PO_STATUS, -- i.invoice_id, i.invoice_num, i.invoice_amount, i.amount_paid, i.vendor_id, -- v.vendor_name, -- p.check_id, c.check_number, h.gl_transfer_flag, h.period_name from ap_invoices_all i, ap_invoice_distributions_all invd, po_headers_all poh, po_lines_all pol, po_distributions_all pod, po_vendors v, po_requisition_headers_all reqh, po_requisition_lines_all reql, po_req_distributions_all reqd, ap_invoice_payments_all p, ap_checks_all c, ap_ae_headers_all h, ap_ae_lines_all l where 1=1 and i.vendor_id = v.vendor_id and c.check_id = p.check_id and p.invoice_id = i.invoice_id and poh.PO_HEADER_ID = pol.PO_HEADER_ID and reqh.REQUISITION_HEADER_ID = reql.REQUISITION_HEADER_ID and reqd.REQUISITION_LINE_ID = reql.REQUISITION_LINE_ID and pod.REQ_DISTRIBUTION_ID = reqd.DISTRIBUTION_ID and pod.PO_HEADER_ID = poh.PO_HEADER_ID and pod.PO_DISTRIBUTION_ID = invd.PO_DISTRIBUTION_ID and invd.INVOICE_ID = i.INVOICE_ID and h.ae_header_id = l.ae_header_id and l.SOURCE_TABLE = 'AP_INVOICES' AND l.SOURCE_ID = i.invoice_id --and poh.segment1 = 4033816 -- PO NUMBER and reqh.segment1 = '501' -- REQ NUMBER --and i.invoice_num = 3114 -- INVOICE NUMBER --and c.check_number = -- CHECK NUMBER --and vendor_id = -- VENDOR ID
===========================================
WITH RECEIPTS
=========================================== -- PROCURE TO PAY CYCLE QUERY WITH RECEIPTS SELECT DISTINCT reqh.segment1 req_num, reqh.authorization_status req_status, -- POH.PO_HEADER_ID, poh.segment1 po_num, pol.line_num, poh.authorization_status po_status, rcvh.receipt_num, rcv.inspection_status_code, -- I.INVOICE_ID, i.invoice_num, i.invoice_amount, i.amount_paid, i.vendor_id, -- V.VENDOR_NAME, -- P.CHECK_ID, c.check_number, h.gl_transfer_flag, h.period_name FROM ap_invoices_all i, ap_invoice_distributions_all invd, po_headers_all poh, po_lines_all pol, po_distributions_all pod, po_vendors v, po_requisition_headers_all reqh, po_requisition_lines_all reql, po_req_distributions_all reqd, rcv_transactions rcv, rcv_shipment_headers rcvh, rcv_shipment_lines rcvl, ap_invoice_payments_all p, ap_checks_all c, ap_ae_headers_all h, ap_ae_lines_all l WHERE 1 = 1 AND i.vendor_id = v.vendor_id AND c.check_id = p.check_id AND p.invoice_id = i.invoice_id AND poh.po_header_id = pol.po_header_id AND reqh.requisition_header_id = reql.requisition_header_id AND reqd.requisition_line_id = reql.requisition_line_id AND pod.req_distribution_id = reqd.distribution_id AND pod.po_header_id = poh.po_header_id --AND POH.PO_HEADER_ID = RCV.PO_HEADER_ID AND rcvh.shipment_header_id = rcv.shipment_header_id(+) --AND RCVH.SHIPMENT_HEADER_ID = RCVL.SHIPMENT_HEADER_ID --AND RCV.TRANSACTION_TYPE = 'RECEIVE' --AND RCV.SOURCE_DOCUMENT_CODE = 'PO' --AND POL.PO_LINE_ID = RCV.PO_LINE_ID --AND POD.PO_DISTRIBUTION_ID = RCV.PO_DISTRIBUTION_ID AND pod.po_distribution_id = invd.po_distribution_id AND invd.invoice_id = i.invoice_id AND h.ae_header_id = l.ae_header_id AND l.source_table = 'AP_INVOICES' AND l.source_id = i.invoice_id --AND POH.SEGMENT1 = 36420 -- PO NUMBER AND reqh.segment1 = '501' -- REQ NUMBER --AND I.INVOICE_NUM = 3114 -- INVOICE NUMBER --AND C.CHECK_NUMBER = -- CHECK NUMBER --AND VENDOR_ID = -- VENDOR ID --AND RECEIPT_NUM = 692237
The requirment is to restrict Libiality code field against updatation , once the invoice validated. The libiality code field is hidden in the form means from show field we have to select that for display. I tried with personalizations on the field as follows :
Go to the Invoice Form with querry one of the invoice :Navigation : Help > Digonostics > Custom Code > Personalize
Condition ========== Trigger Event : WHEN-NEW-ITEM-INSTANCE Trigger Object : INV_SUM_FOLDER.APPROVAL_STATUS_DISPLAY Condition : ${item.inv_sum_folder.approval_status_display.value} ='Validated' Note : We can use IN operator in Condition like ${item.inv_sum_folder.approval_status_display.value} in ('Validated','Never Validated') Processing Mode : Both
Action ======== Type : Property Language : All
Object Type : Item Target Object : INV_SUM_FOLDER.LIABILITY_ACCOUNT Property Name : UPDATE_ALLOWED (ITEM-INSTANCE) Value : False
AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have one or more scheduled payments. An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that itcalculates on invoices that are overdue. Your Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated with an invoice. An invoice can have multiple distribution lines. Your Oracle Payables application automatically creates rows in this table when:
1) you choose a distribution set at the invoice level
2) you import expense reports
3) you match an invoice to a purchase order or receipt; ituses information from the matched purchase order or receipt
4) you import invoices via the Open Interface Import process
5) you select to automatically calculate tax
6) you select to automatically do withholding.
Each invoice distribution line has its own accounting date. When you account for an invoice, your OraclePayables application creates accounting events, accounting entry headers and accounting entry lines for those distribution lines that have accounting dates included in the selected accounting date range for the Payables Accounting Process.The accounting entries can then be transferred over to General Ledger by running the Transfer to General Ledger process which creates journal entries. Values for POSTED_FLAG may be Y for accounted distributions or N for distributions that have not been accounted. Values for ACCRUAL_POSTED_FLAG may be Y if distribution has been accounted and system is set up for accrual basis accounting or N if either distribution has not been accounted or accrual basis accounting is not used. Values for CASH_POSTED_FLAG may be Y if distribution has been accounted and system is set up for cash basis accounting, N if either distribution has not been accounted or system is not set up for cash basis accounting or P if distribution has been partially accounted in the cash set of books. The MATCH_STATUS_FLAG indicates the approval status for the distribution. Values for the MATCH_STATUS_FLAG can be null or N for invoice distributions that Approval has not tested or T for distributions that have been tested or A for distributions that have been tested and approved. Invoice distributions may be interfaced over/from Oracle Assets or Oracle Projects. Your Oracle Payables application sets the ASSETS_ADDITION_FLAG to U for distributions not tested by Oracle Assets; Oracle Assets then adjusts this flag after it tests a distribution for assignment as an asset.
To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project–related invoice distribution to Oracle Projects before you can interface it to Oracle Assets. If the project–related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project–related, with the PA_ADDITION_FLAG set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project–related supplier invoice distribution lines and expense report distribution lines.
For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project–related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Projects; see QuickCodes listing for all the errors. You must correct the rejection reason an try to retransfer the line. For supplier invoice adjustment lines interfaced from Oracle Projects to Oracle Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. For expense report distributions interfaced from Oracle Projects to Oracle Payables via Invoice Import, this value is set to N. This row is never picked up by the Interface Supplier Invoices process based on the AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE = EXPENSEREPORT. For expense report adjustment lines interfaced from Oracle Projects to Oracle Payables which net to zero with another line, thisvalue is set to T. Both lines are associated with the original invoice by the Oracle Projects Interface Expense Reports to AP process. Values for the ENCUMBERED_FLAG are as follows:
- Y indicates aregular distribution that has been successfully encumbered by Payables;
- W indicates a regular distribution that has been encumbered in advisory mode even though insufficient funds existed;
- H indicates a regular distribution that has not been encumbered because it was put on hold;
- Nor null indicates a regular line that has not been encumbered because it has not been looked at yet;
- D is the same as Y for a reversal distribution line;
- X is the same as W for a reversal distribution line;
- P is the same as H for a reversal distribution line;
- R indicates a line to be ignored by encumbrance and approval code because neither the original nor the reversal distributions were looked at and they offset each other so, they can be ignored.
AP_PAYMENT_SCHEDULES_ALL
AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle
Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be ’Y’ to
place a hold on the scheduled payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.
AP_HOLDS_ALL
AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non–matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice–shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Approval), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID,and HOLD_LOOKUP_CODE.
AP_AE_LINES_ALL
An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.
AP_AE_HEADERS_ALL
An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was encountered during the transfer to GL process
